Hide Certain Ranges In Different Worksheets According To The Assigned Cell Value In The First Worksheet
Jan 22, 2010
I have got a macro (from Mr Excel's MVP) that hide certain cells according to assigned cell value
What I need is:
to hide certain ranges in different worksheets according to the assigned cell value in the first worksheet
if sheets1.cell A1= "hi" then
range (" goooo") in sheet 1.hide
range ("deeeee")in sheet 2 .hide
range ("faaaaa") in sheet 3.hide
the macro I had is :
====================================
====================================
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Address = "$A$1" Then
If Target.Value = "hi" Then
Worksheets("Sheet1").Rows(2).Hidden = True
Else
Worksheets("Sheet1").Rows(2).Hidden = False
End If
End If
End Sub
I have a formatting macro that I recorded, nothing fancy just bringing in a few different spreasheets, and formatting them. I made this for someone who really doesn't know excel.
I linked this button to the macro and it works great, except at the bottom of the code I would like to put something that when this has run, it hides the button, so it can't be run again.
I need the correct syntax to consolidate ranges from several worksheets into a summary worksheet - all in the same workbook. I am building the Array on the fly, and I keep getting errors. (Subscript out of range being the most recent).
The code now is opening only one workbook so I can keep it simple. It iterates through the sheets collection, and builds the array.
Private Sub cbConsolidateToRollups_Click() Dim MyArray() As Variant Dim Source As Variant Dim SheetNames As Variant Dim SingleQuote, DoubleQuote SingleQuote = Chr(39) DoubleQuote = Chr(34) 'set the directory to Rollups ToPath = Application.DefaultFilePath & "Cost Tracking" & "Rollups" ChDir ToPath ....................
I have written a VBA procedure which is supposed to hide unneeded columns (based on the a number entered in a certain cell. This is working so far. Since I need to sum only the visible cells in a row i need to clear the contents of a range of cells if they will be hidden. Unfortunately the code i have written runs only to the ClearContents and then starts over from the beginning. If i take out the .clearcontents and put .Select for example instead, the code runs perfectly. Here is the
Sub worksheet_calculate() 'Hide columns that are not needed
Sheet1. Unprotect
Application. ScreenUpdating = False
Dim i As Integer Dim r As Integer Dim s As Integer Dim rngRange As Range
I have a little trouble with my macros. I wrote several different macros, each for a special purpose. Then I created some Buttons, which would make it easy for others to work with them.
But I have now experienced several times, that my buttons don't keep their assigned macro.
I am in need of a macro to hide worksheets that do not match a certain cell value (which I have range named) in the "input data sheet". If this value matches cell value on the other worksheets, then hide all other worksheets and keep the "valid" worksheet visible
NVS_SCN is the range name that needs to be compared to the value in P4 of worksheets 2 thru 4
Example: NVS_SCN (located on the LANDFILL_DATA wksht) = RC_SOUTHLF I want the Macro to hide all worksheets except for the South Region LF_VOL_Trend sheet,
if NVS_SCN = RC_MWESTLF then hide all but Midwest Region LF_Vol_Trend
I am attaching a file. I have seen macros to "unhide" or "Hide Rows or Columns" but I am not adept enough to figure out how to utilize range names and hide all but the one I want. As for "recording" the macro, I didn't know how to do the compare part.
I want to set up a few workbooks with 11 sheets. Sheet 1 will in effect be an index, with True or False values in say cells D5:D14. The True/False values will be generated by form control check boxes in some of the workbooks and by IF(TODAY()>xxxx formulas or similar in other workbooks.
I then want to be able to hide or unhide sheets 2-11 based on the True/False values in cells D5:D14 in sheet 1.
I have a workbook that contains 14 worksheets. I have been looking for vba code that hides 3 of these sheets according to a date value. The date value is situated on sheet 1 cell B7. The 3 sheets I wish to hide are sheets 2, sheets 3 and sheets 4. These 3 sheets are required to be made unhidden until 3 days before date in sheet 1 cell B7. From the date value I want the workbook to hide the 3 sheets and can only be unhidden if a correct password is used to unhide these 3 sheets.
What I'm trying to do is use a variable NumberOfRows (which is defined as an integer) in this following code, but the problem I get is that the NumberOfRows isn't being inserted.
Let's assume that NumberOfRows = 15.
What I would expect to be in cell B1 is =sum(H5:H15), but what is getting placed there is =sum(H5:HNumberOfRows) which of course doesn't give me anything. I'm sure this is an easy syntax thing, but I just can't seem to get the right combination.
I would like to create a macro/vb code that will unhide a worksheet if cell value is greater than zero, or hide the worksheet if the cell value is zero.
I'm not too familiar with VB code and was wondering if this is possible? I have my doubts, since it means that the code must constantly be running in an endless loop, checking to see if the value has changed?
The workbook has multiple sheets, and a cover sheet. Each project has a sheet and the status, costs, updates ect. are updated to the cover sheet.
Problem: What happens is there is a cell which has conditional formatting on it, e.g. If text is Green then colour cell green, if cell text is amber then colour amber and if cell text is Red then colour Red. This works fine.
If the cell text is green then I need a number of rows hidden, if it is amber or red then show the rows.
I have found the following Sub If Target.Value = 1 Then Rows(Target.Row).RowHeight=0 Else End If End Sub
Which I have changed to:
Sub If F4.Value = Green Then Rows("5,6,7,8,9,10.Row).RowHeight=0 Else End If End Sub
So this does not work, need to know why and how to make this work for each sheet I am on e.g. ActiveWorksheet?
I have a workbook with multiple worksheets. Each worksheet corresponds to a certain store fixture estimate. Ont these sheets I have a specific cell where you can input how many fixtures of that type are to be used.
On that sheet also, is a range of cells (ex. Range("A65:F3340")) that needs to be copied to a new summations sheet of total hours to build the project.
If sheet 1 has 1 fixture - the macro should copy the range of cells only once. Sheet 2 has 4 fixtures - tha macro should copy the same range four times appending each set of data tot eh end of the previous, And so on for each fixture sheet.
I have 10 worksheets. I would like to create a macro to find all the "2" values on worksheet 1, and have the actual range that the cell is, compiled into a list -example: I would like the list to be similar to this= (A1,B15 ,C8)
I even tried to record it but it doesnt show me the actual range that the data is found in.
I am creating a series of charts that all include a text box containing "(N=[value])", with different values in each instance, but with each N value listed as a total frequency in each table. I am creating hundreds of these, so if there is a way to automatically pull the correct N value from a cell into the text box, it would make this faster. I have written simple functions in excel tables but have no experience with VBA/macros.
Hide Columns based on Date value in cell (Worksheet Change event)Looking for a worksheet_change event macro that will automatically hide columns based on a value in cell S3. S3 is a data validation pick list of months based on the 1st day of each (e.g. 1/1/2014, 2/1/2014, 3/1/2014, etc.). I have date columns in row 6 from columns T through AQ. These values are 1/1/2014 (T6), 2/1/2104 (U6), 3/1/2014 (V6)....12/1/2015 (AQ6). I would like to hide columns that have a date in row 6 (T6:AQ6) that is LESS THAN (<) the date in S3 after the cell is value is changed.
i have a workbook with two sheets. i have a command button on sheet 1 that inserts another worksheet from another workbook based on a cell value in sheet 1. i would like this specific command button on sheet 1 to be hidden or disabled until a value is entered into a certain cell on sheet 1 that matches a value in a named range on sheet 2.
If Sheets("Labor Summary").Range("A8").Value Like "Function*" Then Sheets("Labor Summary").Rows("7:49").Hidden = True Else Sheets("Labor Summary").Range("9:15,24:49").Hidden = True
The formula in cell A1 of the attached inserts the worksheet name, but it doesn't work when pasted to a group of worksheets. The formula doesn't change from sheet to sheet, yet the results refer to the workheet name where the "paste" was originally done. Does anyone know of a similar formula that would work right when applied once across a group of worksheets?
I have a table that lists names, eligibility and # of people. That is just one worksheet. I have a total of 30 worksheets in that workbook. The column with the names is completely filled out. I entered a number in the eligibility and # of people columns and Excel copied all three cells (name, eligibility and # of people) to the same cells on all of the worksheets. I have no formula in any of the cells, nor do I have anything linked.
I am developing a Workbook where I import data to one worksheet and then disseminate it to other worksheets. I am not sure if I am overthinking the process, but I am having difficulty writing the macro and getting it to run through the entire list of rows.
There are 4 Columns in the one worksheet, named "Update": Column A supplies the name of the worksheet that will be updated Columns B-D contain the data that will update the sheets.
The process that I am trying to write the macro for is a follows: 1. Select/copy the range, starting with B1:D1 on "Update" 2. Navigate to the appropriate worksheet to be update, determined by A1 3. Identify the first blank row, starting with A5 4. Paste the data B1:D1 to A5:C5 (or next available line) 5. Navigate back to "Update" and begin the process again with the next row B2:D2
This process should continue until there is no value in Column B on the "Update" worksheet.
I would like to perform a calculation with each cell in a range from 2 worksheets and place the result in a range on a third worksheet.
I thought I could use the For Each Cell in Range construct but I don't see how to reference the two ranges in a nested pair of For loops. I am sure there must be an simple/elegant way to do this.
I am building a sheet to display deadlines (rows) by project (columns). I would like to have it automatically hide the rows and columns based on the date of the deadline. If the deadline is today or 1 week from today the associated rows and columns should be visible, otherwise, I would like to hide them.
My table is A1:N9, with A1 being a blank, row names A2:A9, and column names B1:N1.
I am trying to use an ActiveX CommandButton on Sheet40 to do the following:
Sheet40 (E31:AN39) = sum of (E31:AN39) for sheets 6-15 PROVIDED that cell D3= "y" in those sheets
So the steps are: (1) Among sheets 6-15 select those in which cell D3 = "y" (2) Set the range of (E31:AN39) in sheet40 to the sum of the same range in the selected sheets
ie cell E31 = sum of cells E31 in selected sheets... cell E32 same... cell AN39 same
I have a worksheet in a workbook that is for 1 of 8 sub-projects. I have all the macros running perfectly and everything looks fine. Now, I have to duplicate that sheet 7 more times and create a Summary sheet for the entire workbook. My question is this:
How can I make it easy to set up named ranges in the new sheets so I don't have to pick each new range in each sheet and define names individually?
Example of a small macro... Sub ActCurrJTDtoActPrevJTD() Application.Goto Reference:="AActHrsCurrPeriod" Selection.Copy Application.Goto Reference:="AActHrsPrevPeriod" Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone, SkipBlanks:= _ False, Transpose:=False Range("A1:V1").Select Application.CutCopyMode = False End Sub
I need to duplicate AActHrsCurrPeriod into BActHrsCurrPeriod and CActHrsCurrPeriod, up to an H version.
Clicking the button opens the MSgbox then on clicking Yes to run the code it stops on - Range("E3:BU98").Select - saying range selection method failed, and i cant understand why it seems to be such a simple piece of code.
Private Sub CommandButton2_Click() Dim msg2 As Integer msg2 = MsgBox("Has a back up copy been saved?" & vbCr & "Are you sure you want to clear all existing products and their results?", vbYesNo, "Delete Products?") If msg2 = 6 Then Worksheets("Input Record").Activate Range("E3:BU98").Select Selection.ClearContents Worksheets("Results record").Activate Range("E3:CA23").Select Selection.ClearContents Worksheets("Input Page").Activate End If End Sub
I would like to link a sheet to another sheet in the same workbook. The link would indicate what week of information is needed. I would like the source (weekly information )page to go to the top of the page with the requested data. If I use a hyperlink it directs to the source page but this data could be several rows down. How do I create a link and have the source page provide the data at the top of the page?
I have three sheets: On sheet 1, I have a list of products ( Down ) with Bought and sold info by time period ( Across ). On sheet 2, I have the same list of products from sheet 1 ( May not be in the same order ) with " Attributes " associated to them ( e.g. male, female etc ). On Sheet 3: I would like to add the Bought and sold info by time period on sheet 1 by attribute as per sheet 2
I have attached a spreadsheet, which has a master data sheet and 2 other worksheets (Sheet 1 and Sheet2) that have linked Choose Functions from the main data worksheet.
I would like to send copies of Sheet 1 and Sheet 2 to seperate people, however would not like them to be able to view each others worksheets or the Master Data sheet, as it is sensitive data.
Does anyone know how I could create a macro, that enables me to click a button which would protect & hide two of the worksheets including the name of the worksheet (e.g. protect & hide worksheet "Data" and "Sheet 2"). This way I could send the entire spreadsheet to someone while hiding the sensitive information.
i have 5 sheets on my spreadsheet, only one of them is a front sheet, which is used by a agent. Now i need to hide the other 4, but still have them working in the back ground, but when i right click, its not highlighted? The book is not restricted and the sheets or not protected, thou i have tried both ways.
