VB code to hide selected ws

pilot

Active Member
Joined
Feb 17, 2002
Messages
345
WS tab names end in either an alpha character or a 1, 2 or 3. What code (tied to a button) will permit hiding or unhiding of certain sheets such as "unhide all sheets ending in a 2 and hide all others"? How about if I want to just toggle the "hid" condition of all sheets ending in a 2?
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).
Use the following code routines...


Sub hide_pages()
For sheetno = 1 To ActiveWorkbook.Sheets.Count
If Right(Sheets(sheetno).Name, 1) = 2 Then
Sheets(sheetno).Visible = False
Else
Sheets(sheetno).Visible = True
End If
Next
End Sub

Sub toggle_pages()
For sheetno = 1 To ActiveWorkbook.Sheets.Count
If Right(Sheets(sheetno).Name, 1) = 2 Then
If Sheets(sheetno).Visible = True Then
Sheets(sheetno).Visible = False
Else
Sheets(sheetno).Visible = True
End If
End If
Next
End Sub




...substituting the IF clause for whatever you need.
 
Upvote 0
The following code will hide all worksheets in the current workbook that end in 2:

For i = 1 To ThisWorkbook.Worksheets.Count
If Right(Worksheets(i).Name, 1) = 2 Then Worksheets(i).Visible = False
Next i

Is this what you wanted?
 
Upvote 0
Thanks guys, this is getting there. Daleyman, in both of your Subs, the 3rd line caused an abortion (?).
<If Right(Sheets(sheetno).Name, 1) = 2 Then>

Damon's suggestion works fine but I want to take it a step or two beyond. What is the code to display all sheets like "*1 or *2"?

How about displaying all sheets NOT like "*1 or *2"?

One more, display all sheets like "*2" and the sheet titled "XYZ"?
 
Upvote 0
Answer to pilot's follow-on question:


Damon's suggestion works fine but I want to take it a step or two beyond. What is the code to display all sheets like "*1 or *2"?

Sh.Visible = Sh.Name Like "*[1-2]"


How about displaying all sheets NOT like "*1 or *2"?

Sh.Visible = Not (Sh.Name Like "*[1-2]")

One more, display all sheets like "*2" and the sheet titled "XYZ"?

Sh.Visible = (Sh.Name Like "*2") Or Sh.Name = "XYZ"
 
Upvote 0

Forum statistics

Threads
1,213,536
Messages
6,114,208
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top