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!

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


On 13 Sty, 14:53, Tony S. wrote:
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,

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" wrote:

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


On 13 Sty, 14:53, Tony S. wrote:
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!

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

On 13 Sty, 15:58, Tony S. wrote:
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" wrote:
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

On 13 Sty, 14:53, Tony S. wrote:
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!- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

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" wrote:

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

On 13 Sty, 15:58, Tony S. wrote:
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" wrote:
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

On 13 Sty, 14:53, Tony S. wrote:
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!- Ukryj cytowany tekst -

- Pokaż cytowany tekst -

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


On 13 Sty, 22:11, Tony S. wrote:
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" wrote:
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

On 13 Sty, 15:58, Tony S. wrote:
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" wrote:
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

On 13 Sty, 14:53, Tony S. wrote:
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!- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -- Ukryj cytowany tekst -

- Poka¿ cytowany tekst -

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.

"Tony S." wrote:

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!