Hi I am trying to create a script where it hides all sheets except for the specified ones, however, other people either change the name of the sheet, or add their own. I am having issues where the script uses the sheet index as opposed to the name.
Sub HideSheets() Dim sh As Worksheet Application.ScreenUpdating = False For Each sh In ActiveWorkbook.Sheets If sh <> sheet1.Name or sh <> sheet2.Name Then sh.Visible = xlSheetHidden Next Application.ScreenUpdating = True End Sub
Change this
toIf sh <> sheet1.NameIf sh.Name <> "sheet1"
Good Luck...
I don't presume to know what I am doing, however, just like you, I too started somewhere...
One-day, One-problem at a time!!!
If you feel I have helped, please click on the [★ Add Reputation] to left of post window...
Also....Add a comment if you like!!!!
And remember...Mark Thread as Solved...
Excel Forum Rocks!!!
Fantastic! Thank you!
Just went over your code...can't see it working...Rather use this:
Option Explicit Sub HideSheets() Dim sh As Worksheet Application.ScreenUpdating = False For Each sh In Worksheets If sh.Name <> "Sheet1" And sh.Name <> "Sheet2" Then sh.Visible = False End If Next Application.ScreenUpdating = True End Sub
This only works if the sheet is named "Sheet1", I would prefer if it was .sheet1 - the indexed number, this way if someone changes the sheet name the macro wont brake...
Option Explicit Sub HideSheets() Dim sh As Worksheet Application.ScreenUpdating = False For Each sh In Worksheets If sh.Name <> Sheets(1).Name And sh.Name <> Sheets(2).Name Then sh.Visible = False End If Next Application.ScreenUpdating = True End Sub
There are currently 1 users browsing this thread. (0 members and 1 guests)
Posting Permissions
LinkBack URL
About LinkBacks
Register To Reply
Bookmarks