I would like to have a macro that will hide a column based on the value in the row. I have multiple sheets and if row 4 (or more specific, B4:AL4) contains a zero, then I'd like the whole column to be hidden on that sheet. I'd like to be able to run the macro and it evaluate every worksheet.
View 5 Replies
I want by using some code I've seen on this forum or using the macro writer and then tweaking the code. So with that said, I've written the attached code but I know there is probably an easier way to write it. It cycles through about 12 sheets using the same below code, but I didn't list that code.
Sub Hide_Rows()
Dim i As Integer
For i = 3 To 418
Sheets("AFA - UMBI").Select
If ActiveSheet. Range("b" & i).Value = "2008-2" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2008-3" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2008-4" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-1" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-2" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-3" Then
Rows(i & ":" & i).EntireRow.Hidden = True
ElseIf ActiveSheet.Range("b" & i).Value = "2009-4" Then
Rows(i & ":" & i).EntireRow.Hidden = True
End If
Next i
End Sub
There are 2 sheets in the excel worksheet. in one sheet there would be drop downs
the 2nd sheet will have 4 columns A,B,C,D) Depending on the selection in sheet1. If a1 = Y then columnA in the 2nd sheet should be hidden. If a1 = N then columnB in the 2nd sheet should be hidden. how can this be done..The key is the columns to be hidden are on a different sheet and that is where I am having the difficulty.
What would be the macro if i need to hide multiple select sheets? I'm working on a test with 12 sheets. Even number sheets contains the fields that they need to answer and the Odd number sheets contains the formula for score computing including the answers so it needs to be like this:
Sheet 2 - The test
Sheet 3 - Must be hidden
Sheet 4 - The test
Sheet 5- Must be hidden
Sheet 6- The test
Sheet 7 - Must be hidden
I already have the code to unhide all sheets, just need the macro to hide specific sheets like the ones above.
I have a workbook with 31 sheets. Each sheet has 15 textbox button that call macros.
I would like to hide 1 textbox on all sheets until another macro is called. I know how to get the textbox names to be able to hide them, But because these text boxes were copied most of the are the same name, however on severl sheets they are different names "Textbox 4 on most sheets but it could be textbox 34 on others. The ones I want to hide all have the same text label. Is it possible to get VBA to return the label text.
I got a quite huge excel file with multiple sheets. For convenience sake I want to group and hide all the sheets not necessary for the viewer.
View 14 Replies View RelatedI wrote a macro to select multiple sheets by name and hide them, but I keep getting the following error message:
"Object variable or with block variable not set".
Below is the macro:
Sub HideSheets1A()
Dim ws As Worksheet
Application.DisplayAlerts = False
If ws.Name = "Variance Evaluation" Or "Investment" Or "Costs & Incentives" Or "Revenues Total" Then ws.Visible = False
End Sub
I use Excel 2003
I have read several related threads and tried to adapt their answers to my application with no success. I am trying to write a macro that will hide rows based on that row's value in a certain column. Specifically, column AB contains sums and if a sum equals 0 I want to hide that row. The sums start at AB5 and go to AB118 but there are 4 gaps in the column at rows 25, 47, 70, and 94.
View 9 Replies View RelatedI would like to hide rows for data that does not meet specific criteria. For example: If a user selects room number 101 from a drop down in B1, I want to filter data in range A3:F1000 to show me only room 101 rows (A column) where C column contains a value larger than zero OR D column contains a value larger than zero OR E column contains a value larger than zero. I do not want it to return rows where 101 may be in other columns beside A.
Sample:
A B C D E F
__________________________________
1 ROOM: 101
2
3
4 101 XX 1.2 0 0 P
16 101 YA 0 0 1.1 L
23 101 JJ 3.2 2.1 0 L
55 101 JJ 0 0 1 P
So, if a row contains 101 in column A and all three values in columns C, D, and E equal 0, then those rows will be hidden.
I have used this code below to split a large excel file into multiple sheets from matching column data, but now I need to split it by a partial match (set number of characters from the beginning) from beginning of the column data.
For Example:
[Code]....
So with the code provided below using column 3 I would get 10 different sheets since none of the data in the column is identical. I want to modify the code (or come up with new code) so I can set the number of characters to compare from the beginning of the data in the set column and split into sheets based on that. So if I set it to the first 4 characters in column 3 I would receive only 5 sheets sheets: Safe, Fail, Dont, Poop, & 21-4.
What are the modifications or new code needed for this? I have searched for a bit with no luck, just keep finding code to check the full cell data for matches in a set column like this code I have:
SPLIT DATA FROM ONE SHEET TO MULTIPLE SHEETS
[Code] ......
I am trying to find a way to copy the contents of an entire row to a new sheet in a workbook based on the value of a column, specifically column K with the value of "good". There are 3 sheets that im searching. This is where I run into problems. Im looking to start with sheet1, and copy all rows deemed "good" into entries on sheet 4. Then search sheet2 and copy all relevant entries into the next available row on sheet4, and then repeat on sheet3.
View 2 Replies View RelatedI have multiple sheets in one file. I need a code (that will be ran at random) to look at all the sheet names and the ones that end in the word "Archive" to move to another file.
View 5 Replies View RelatedI have an excel document with multiple excel sheets(sheet1, sheet2...etc), now every sheet contains a cell "total".
Now I want to copy the row containing "total" from all the sheets into another sheet called "report".
i want a macro to search all of column A in multiple sheets for a date that has been put into an input box.
the matching dates have data corresponding to them in that row. i want the date and data from the row to then be copied into a new sheet.
i would like to hide different sheets at one shut. i tried this, but did not work:
Dim sh As sheet
For Each sh(1 3 5 7 9).Visible =xlSheetVeryHidden
I am trying to make a save&close workbook macro.
I found several examples on google, but unfortunatly it conflicts with another macro I use for forceing users to enable macros (hide all sheets except one if macros are disabled).
The attached file is an example contaning the save&close code and the show/hide sheets depending on macros enabled.
If the file is opened with macros disabled then only one sheet will be visible.
If the file is opened with macros enabled other sheets are visible.
The problem if that this code uses a custom save, witch makes the save&close not save... (in module1 and in ThisWorkbook)
The pourpose of the save&close is to make sure some users don't forget the excel open and thus block access to it. So if a certain idele time passes excel has to save and close without any confirmation messages.
I have over 60 sheets within a workbook. There are some sheets that I want to hide or unhide depending upon the macro. I have the MR and searched in several areas but keep coming up blank with how to either select or hide these sheets.
This is from the MR:
Sheets(Array("Process", "Utilities", "CodeRef", "DataRef (3)", "DataRef (2)", "DataRef", "Dept Summary New", "Summary_Dept", Summary_ Monthly")).Select
When I try to use this in the code it errors out.
I see this forum and the example works perfectly in Excel 2003.. But we are using Excel 2007 now and it doesn't appear to be recognizing the user and is always refering to the "else" in the vb codes. Does this code for not work in 2007? Oh and I am refering to Post #10 on there with the attachment that will hide different sheets depending on which user is getting on the file.
View 3 Replies View RelatedI have a workbook which has roughly 50 sheets. What I'm trying to do is automatically hide/unhide sheets based on the cell values in the first sheet. So in sheet1 cell A1 i would a value of FALSE which would trigger sheets1, 2, & 3 to hide, when that value changes to TRUE then those same sheets would unhide. I need to replicate that for the 10 corresponding sets of sheets, but for each grouping of sheets a different cell in sheet1 would be the trigger, cell A2 = sheets 4 - 10, cell A3 = sheets 11 - 20, etc.
View 9 Replies View RelatedI made one file with 13 sheets.
sheet1 tab name is : MAIN
and other sheet tab name like following
2. xyz-Sales
3. xyz-Rev
4. xyz-SSN
5. xyz-ddn
6. abc-Sales
7. abc-Rev
8. abc-ddn
9. abc-ssn
10. ddd-sales
11. ddd-Rev
12. ddd-ssn
13. ddd-ddn
In Main sheet There are 3 buttons
1 . XYZ
2. abc
3. ddd
when user press on xyz button then only xyz sheets (like sheet 2 to 5) are shows to user and other sheets are very hide
if user press abc button then only abc sheets (like sheet 6 to 9) are shows to user and other sheets are very hide
i don't want to use
Sheet2.Visible = xlSheetVeryHidden
i want to use finde xyz sheet tab name and shows and other are hide.
I want to hide a sheet in an excel workbook based on a Yes or No answer in the first sheet in the file. Is there an easy way to do this?
The above operation will be repeated up to 10 times, but the decision to hide or not hide each sheet will depend on only one answer.
In some cases I want to hide several sheets based on one answer.
The overall objective is to have a flexibly sized workbook suitable for a range of users who will not be faced with sheets which are not relevant for their individual circumstances.
I am making a new stock card system to keep track of stock at work for a new storeman working in my department. What I want to be able to do it hide/show sheets bases on their colour, the reason I want to do this is because I want the new storeman to be able colour code products and only show certain ones at any one time to stop him from having to sort through loads of sheets, but also for him to be able to add extra sheets and asign them a colour and not need me to modify code and put it into category X So in summary, I want code that will hide all sheets that colour is red or blue etc and not need updating when new sheets are added.
View 4 Replies View RelatedI have a situation where i would like to hide and delete a few sheets in my worksheet.
there is a few sheets where i want it to be constantly visible and one sheet is based on a cell reference.
the remaining irrelevant sheets i would like it to be hidden and deleted.
'always visible are sheets that are constantly visible in all files' 'visible msds is the sheet where my product data is being pulled out based on a input i enter on my first sheet' the sheets i want to hide and delete will be the remaining irrelevant "MSDS" that i would like to remove through macro.
continued from: [URL]
Option Explicit forces explicit declaration of all variables in the code. That is why i had to declare ws as worksheet using the Dim statement before using it in the code. It is recommended to have it - however for this code it wouldn't have mattered.
I'm not sure I exactly follow what you imply here. But yes, you can hide all the sheets at first and then "unhide" at the start of the macro. Let's say you have a "Start" sheet.. then:
[Code] .....
The workbook is used by other people too. I cannot just tell them to hide the sheets, it defeats the purpose. What I want is, when macro is disabled, they will only see the START sheet, otherwise, all sheets will be visible. To add to this, a new sheet is added everyday. Sheets name are calendar dates in mmdd format
I want to run 2 different macros:
Macro 1- hides Sheet1 and unhides Sheet2
Macro 2- Hides Sheet2 and unhides Sheet1
I used the macro recorder to attempt to make this work but am running into a problem if Macro1 is run two times consecutively. In this situation the macro displays a debugging error b/c Sheet1 is hidden. Is there a way to get around this...possibly using an if then statement?
I have a workbook that I edit daily then pass along to another team. All of my modules are passwork protected and there are also several sheets in the workbook that I hide before email it to the other team. I hide the sheets by changing the sheet properties in the VBA editor to "2 - xlSheetveryhidden".
This isn't a very time consuming process but I am wondering if there is a way to automate it through VBA?
I tried the the fail safe of "see what happens when I record it" but all I got was an empty Module.
I have 16 sheets and 4 additional sheets that will kind of 'Group' these 16sheets. For example: I have 'Sheet1', 'Sheet2', 'Sheet3'......, 'Sheet16'.
4 additional Sheets are: 'Group1', 'Group2', 'Group3', 'Group4'.
I need a help with macro so that when this workbook is open all 20 sheets ('Sheet1', 'Sheet2', 'Sheet3'......, 'Sheet16') will go into hiding and only 4 additional Sheets ('Group1', 'Group2', 'Group3', 'Group4') will be visible. Now, these 4 sheets will have the command button links to the following sheets:
Sheet 'Group1':'Sheet1', 'Sheet2', 'Sheet3', 'Sheet4', 'Hide All'
Sheet 'Group2':'Sheet5', 'Sheet6', 'Sheet7', 'Sheet8', 'Hide All'
Sheet 'Group3':'Sheet9', 'Sheet10', 'Sheet11', 'Sheet12', 'Hide All'
Sheet 'Group4':'Sheet13', 'Sheet14', 'Sheet15', 'Sheet16', 'Hide All'
This being said, when you click on each command buttons, the respective sheets will open up and when click on 'Hide All', all of the open sheets for that *additional sheet' for example sheet 'Group1' will go into hiding again.
I have a workbook wherein I have 7 sheets.Lets say they are called Tom, Peter, John, Sia, Mia, Tia and "Home Page". I have 2 buttons for Report 1 and 2 to which I want to assign the macros.I also have a table wherein I have defined which sheets I want to show. First Column of table has sheet names from A2:A6(Home Page,Tom, Peter, John, Sia, Mia, Tia). Column 2 has report 1 sheets - Home Page, Tom, John, Mia and Column 3 has report 2 sheet names- Home Page, Peter, Sia, Tia
What I want to do is, if I click on "Report 1" button, I only want to show sheets whose names are there in cells under report 1 so for report 1 it will be Home Page, Tom, John, Mia. For Report 2, it will be Home Page, Peter, Sia, Tia. Since I have many reports I want this to be one macro. Stepwise, here is what I want
1. Click on button for Report, macro should check which report I am referring to and select the range on basis of that. Report 1 = column B, if Report 2, Range is column C.
2. Basis the range I want sheets to show or hide.
excal VBA programming.I have attached the file name "help" for your easy explanation purpose.
1. Is it possible to hide sheet nos. 1,2,3,4 & unhide the sheet as wished by me by puting the value (1or 2 or 3 or 4) in B3 cell.
2.There are per day production rate in E18 to E22 cell. Now whenever I will give value in H18 or H19 or H20 or H21 or H22, it will check whether the value is same with the respective E 18 or E19 or E20 or E21 or E22 cell. If both the values are not equal then give a message box "WARNING!!! YOUR VALUE IS NOT SAME". Can it be possible by creating VBA programming.
I am looking for a macro to replace the values in a range of cells with a specific array for each column heading
Here is what I have
Temp1
Temp2
Temp3
0
0
0
1
1
50
[Code] ....
And here is the expected result of the macro
Temp1
Temp2
Temp3
no
false
min
[Code] ........
Please note there is only one Active Sheet.
