Conditionally hide worksheets

I'm not sure what you mean by a worksheet "pertaining to" the value of cell A1. But here's some code that might help you start:

Sub ConditionalHide()
For i = 1 To Sheets.Count
Sheets(i).Activate
If Range("A1").Value = "X" Then
Sheets(i).Visible = False
Else: Sheets(i).Visible = True
End If
Next i
End Sub

-Ben

As an example: I have a workbook with about 40 sheets. Each sheet is for an indiviual employee. When I enter the facility that they work at in A1 I would like to hide all other sheets and only show me the sheets for the employees that work at that facility. Hope this helps to clarify. Also, I am a VBA newbie (don't know it at all!), would I need to change anything on the above script other than X?

Okay, now I have a better idea of what you want. This code should work, but you'll have to change a few things.

The macro pulls the value from Sheets(1), which is the sheet all the way on the left. The hide routine will also skip this sheet, because I'm assuming you don't ever want it to be hidden.

On the sheets that the macro will hide or not hide depending on whether or not they meet your condition, the macro looks in cell A1. You'll need to change this (If Range("A1").Value = cRange Then...) to the cell that stores the location of the employee.

If you'll be sharing the workbook and don't want others to be able to view the hidden sheets, you can change Visible = False to Visible = xlVeryHidden. That will make it so the sheets will not appear in the Format > Sheets > Unhide dialog box, and can only be unhidden with VBA.

Sub ConditionalHide()
Dim cRange As String
cRange = Sheets(1).Range("A1")
For i = 2 To Sheets.Count
Sheets(i).Activate
If Range("A1").Value = cRange Then
Sheets(i).Visible = False
Else: Sheets(i).Visible = True
End If
Next i
End Sub

-Ben

Where do I enter the facility in the script? Do I replace cRange with the actual name of the facility?

The macro gets the name of the facility from Sheets(1).Range("A1"), which is the cell, A1, on your first worksheet. It assigns that name to the variable, cRange. You can change that reference to any cell you want to pull the facility's name from. If you want to hard-code the name of the facility, change this statement:

cRange = Sheets(1).Range("A1")

to

cRange = "Milwaukee"

Or whatever location you want. Just make sure you put the name in parenthesis.

-Ben

Oh, I get it. Thanks so much for the help! The macro gets the name of the facility from Sheets(1).Range("A1"), which is the cell, A1, on your first worksheet. It assigns that name to the variable, cRange. You can change that reference to any cell you want to pull the facility's name from. If you want to hard-code the name of the facility, change this statement: cRange = Sheets(1).Range("A1") to cRange = "Milwaukee" Or whatever location you want. Just make sure you put the name in parenthesis. -Ben

: Where do I enter the facility in the script? Do I replace cRange with the actual name of the facility? :

Ok, here we go with the newbie problems.....When I enter this code all it seems to do is hide everything or if I tinker with it, unhide everything. Am I just too dumb to figure this thing out? Oh, I get it. Thanks so much for the help! : The macro gets the name of the facility from Sheets(1).Range("A1"), which is the cell, A1, on your first worksheet. It assigns that name to the variable, cRange. You can change that reference to any cell you want to pull the facility's name from. If you want to hard-code the name of the facility, change this statement

Sorry Sam, I made a mistake before. The code I gave you will hide all of the sheets that meet your criteria, when you wanted to hide the ones that don't meet your criteria. My mistake. But you can correct it by changing:

If Range("A1").Value = cRange Then

to

If Range("A1").Value <> cRange Then

As far as changing things goes, just be careful of what you change. What cell on your employee sheets contains the location? Change the A1 in this line to that cell:

If Range("A1").Value = cRange Then

Using Sheets(1) can be a little tricky. You could try changing the 1 to the name of your main sheet, like this: Sheets("Main").Range("A1")

I used Sheets(1) because the hide routine starts on Sheets(2), so that the macro won't hide your main sheet if it doesn't meet your condition.

If all else fails, try the following code. It will prompt you for the Location that will be your hiding/unhiding condition:

Sub ConditionalHide()
Dim cRange As String
cRange = InputBox("Enter location:", "Location")
' For loop starts on sheet 2
For i = 2 To Sheets.Count
Sheets(i).Activate
' If the sheet has the location, don't hide it. Otherwise hide it.
If Range("A1").Value <> cRange Then
Sheets(i).Visible = False
Else: Sheets(i).Visible = True
End If
Next i
End Sub

Sorry for my mistake before!

-Ben

Ok, here we go with the newbie problems.....When I enter this code all it seems to do is hide everything or if I tinker with it, unhide everything. Am I just too dumb to figure this thing out? : Oh, I get it. Thanks so much for the help!

Ben,
Thanks for all your help on this. Finally got it to work! One last quick question. When the macro runs it goes through and finds all the sheets I am looking for but I end up on the last sheet that matches my criteria. Is there a way to make it end up back on the first sheet? Does this make sense?

Sorry Sam, I made a mistake before. The code I gave you will hide all of the sheets that meet your criteria, when you wanted to hide the ones that don't meet your criteria. My mistake. But you can correct it by changing: If Range("A1").Value = cRange Then to If Range("A1").Value <> cRange Then As far as changing things goes, just be careful of what you change. What cell on your employee sheets contains the location? Change the A1 in this line to that cell: If Range("A1").Value = cRange Then Using Sheets(1) can be a little tricky. You could try changing the 1 to the name of your main sheet, like this: Sheets("Main").Range("A1") I used Sheets(1) because the hide routine starts on Sheets(2), so that the macro won't hide your main sheet if it doesn't meet your condition. If all else fails, try the following code. It will prompt you for the Location that will be your hiding/unhiding condition: Dim cRange As String cRange = InputBox("Enter location:", "Location") ' For loop starts on sheet 2 For i = 2 To Sheets.Count Sheets(i).Activate ' If the sheet has the location, don't hide it. Otherwise hide it. If Range("A1").Value <> cRange Then Sheets(i).Visible = False Else: Sheets(i).Visible = True End If Next i Sorry for my mistake before! -Ben : Ok, here we go with the newbie problems.....When I enter this code all it seems to do is hide everything or if I tinker with it, unhide everything. Am I just too dumb to figure this thing out?