Hide Sheets by Report User

[linesy]

Hi,

I developed some pivot table reports and macro'd out pdf'ing them for the users but now management want the users to be able to manipulate the data themselves !! The file is 31 sheets and I'm figuring they'll only want to see the sheets that are relevant to them so is there a way to macro it out?? Perhaps when the file opens it asks, "Who do you want to see?" Jack? Joe? or Rudy?, Month or YTD, or Both? or Everything? and it will hide the sheets that are not selected. The words "month" and "YTD" is in Cell "N1" and the individuals names are in Cell "M1".

This would be a great help and cut down on the filtering through sheets.

Thanks so much

 

[jakeman]

Just a thought but it might make the process easier if you were able to condense the sheets down by putting month and ytd pivot table reports into the same sheet. In fact, pivot tables have a very useful function where you can group by dates so that you can show all of the months grouped together.

If you could do that, you might then create a inputbox with vba attached to a button that when the user clicks on it, they can put in their name and then all of the sheets not needed will be hidden.

I can tinker around with some code for you. Confirm whether you can consolidate the sheets first.

 

[Michael M]

another way to consider would be...using Select Case and having the user enter their name, and based on that name, hide / unhide certain sheets

 

[linesy]

Thanks Jakeman. So I've managed to consolidate the sheets down, thanks for the suggestion. Can you tinker for me :D

 

[linesy]

Thanks Michael, I will look into how that would work?

 

[jakeman]

Thanks Jakeman. So I've managed to consolidate the sheets down, thanks for the suggestion. Can you tinker for me :D

try this:

Sub HideAllSheetsButOne()
Dim sht As Object
Dim sheetName As String

sheetName = InputBox("Please enter Sheet name that you want to see: (Jack, Joe, Rudy)")


For Each sht In Sheets
If sht.Name <> sheetName Then
sht.Visible = xlSheetHidden
End If
Next sht

End Sub

 

[linesy]

Works great Jakeman......thanks so much :D