Hide/Show some worksheets

[Tony S.]

Is there a way to use VB code to hide/show a group of worksheets at once,
possibly using a wildcard? I have a worksheet that contains many sheets that
need to be Veryhidden and Visible quickly and easily without having to go
thru the Format/Sheet/Hide menu. Each worksheet is named for a week of the
year. (i.e. (Wk 1) 12-1-2008, (Wk 2) 12-8-2008,(Wk 3) 12-15-2008 etc). The
current week will always be visible. Thanks!

 

[Jarek Kujawa]

the following code will hide/show relevant woksheets on each workbook
opening

press ALT+F11, open ThisWorkbook module, select Workbook_Open and
paste the code

Private Sub Workbook_Open()
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <=
DateSerial(Year(Now()), Month(Now()), Day(Now())) _
And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) >
DateSerial(Year(Now()), Month(Now()), Day(Now())) Then
ws.Visible = xlSheetVisible
Else
ws.Visible = xlSheetVeryHidden
End If
Next ws

End Sub

 

[Tony S.]

Jarek,

When I run this I get "Run-time error '13' Type mismatch"

I may have pasted the code in the wrong location. I opened the VB editor and
did not see a Workbook_Open. I pasted it in the "ThisWorkbook" under
"Microsoft Excel Objects". Should it be pasted in a module? I am running
Excel 2003.

 

[Jarek Kujawa]

double-click on ThisWorkbook module
you should see two windows to the right 1. (General) and 2.
(Declarations)
click on 1. (General) and select Workbook
in 2. select "Open" from the list of events

the result should be:

Private Sub Workbook_Open()

End Sub

in between those 2 lines paste the following code:

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <=
Now() _
And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) >
Now() Then
ws.Visible = xlSheetVisible
Else
ws.Visible = xlSheetVeryHidden
End If
Next ws

save and close yr file. from now on every time you open it and switch
the macros on, the code should do what you expect

HIH

 

[Tony S.]

Jarek, Your explanation was perfect. I was able to follow it exactly.
However, after I save and reopen the file, I still get "Run-time error '13'
Type mismatch". Does it have anything to do with how the sheets are named? I
can rename them to anything, (like sht1, sht2, sh3, or #1, #2, #3 etc) but I
stiil need one sheet named "Current Status" to be open and active.

Is there a way to hide all the sheets and unhide the "Current Status" sheet?
It doesn't necessarily need to run on opening the file. Ideally, they would
all be hidden until I ran a keyboard macro.

Thanks!

 

[Jarek Kujawa]

did not expect any worksheet to have a name following different
pattern than 12-1-2008, etc.

this probably IS the reason for Excel's behavior = run time error

try to replace
If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <=
Now() _
And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) >
Now() Then

with

If (DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <=
Now() _
And DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) > Now
()) _
Or ws.Name = "Current Status" Then

and let me know if it worked

also if you don't want the whole process to be activated on workbook
opening than delete the Private Sub Workbook_Open code from
ThisWorkbook module
instead Insert->Module and paste the code "here" under a new name (not
Private Sub Workbook_Open!), say it is "sth"

then Tools->Macro->Macros->find/select your "sth" macro, click on
Options, insert a keyboard shortcut

 

[Tony S.]

Jarek, I appreciate your time and efforts. I renamed each tab to be a date
only (i.e "12-1-2008", "12-8-2008", "12-15-2008" etc).

When I run your revised code pasted in a module, it returns the "Compile
error: Else without If" and the Else in the code is highlighted blue.

With your first code it returns the same "Run-time error '13: Type mismatch".

**************
Sub sht()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <=
DateSerial(Year(Now()), Month(Now()), Day(Now())) And
DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) >
DateSerial(Year(Now()), Month(Now()), Day(Now())) Then ws.Visible =
xlSheetVisible
Else
ws.Visible = xlSheetVeryHidden
End If
Next ws

End Sub

 

[Jarek Kujawa]

don't know
looks like everything is OK to me
;-(

Jarek, I appreciate your time and efforts. I renamed each tab to be a date
only (i.e "12-1-2008", "12-8-2008", "12-15-2008" etc).

When I run your revised code pasted in a module, it returns the "Compile
error: Else without If" and the Else in the code is highlighted blue.

With your first code it returns the same "Run-time error '13: Type mismatch".

**************
Sub sht()

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets
    If DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name)) <=
DateSerial(Year(Now()), Month(Now()), Day(Now())) And
DateSerial(Year(ws.Name), Month(ws.Name), Day(ws.Name) + 7) >
DateSerial(Year(Now()), Month(Now()), Day(Now())) Then ws.Visible =
xlSheetVisible
    Else
ws.Visible = xlSheetVeryHidden
    End If
Next ws

End Sub











- Poka¿ cytowany tekst -

 

[Tony S.]

Jarek,

I was able to get the code I was looking for from Dave Peterson in the
Programming section...

Option Explicit
Sub testme()
Dim sh As Object

'make sure that there's always one sheet visible first
Worksheets("Current status").Visible = xlSheetVisible

For Each sh In ActiveWorkbook.Sheets
If LCase(sh.Name) = LCase("current status") Then
'skip it
Else
If LCase(sh.Name) Like LCase("wk*") Then
sh.Visible = xlSheetHidden
End If
End If
Next sh
End Sub

Thanks for you atempt.