Macro to hide sheets based upon cell value
 

I need help with 3 macros for hiding sheets:

1) I would like to hide several sheets based upon the value of a cell i.e.
A1 within the sheet to possibly be hidden.

2) I need a macro to unhide all hidden sheets. Some sheets are not hidden.

3) I need to hide certain sheets using the name of the sheet.

Thanks in advance for your help!
Joe M.

Macro to hide sheets based upon cell value
 

#1.

Dim wks as worksheet
for each wks in activesheet.worksheets
if lcase(wks.range("a1").value) = lcase("hideme") then
wks.visible = xlsheethidden
end if
next wks

#2

Dim wks as worksheet
for each wks in activesheet.worksheets
'don't bother to see if it's visible or not
wks.visible = xlsheetvisible
next wks

#3.

Dim wks as worksheet
for each wks in activesheet.worksheets
if lcase(wks.name) like lcase("*somecommonstring*") then
wks.visible = xlsheethidden
end if
next wks

Remember that with #1 and #3, at least one sheet has to be visible.

Joe M. wrote:

I need help with 3 macros for hiding sheets:

1) I would like to hide several sheets based upon the value of a cell i.e.
A1 within the sheet to possibly be hidden.

2) I need a macro to unhide all hidden sheets. Some sheets are not hidden.

3) I need to hide certain sheets using the name of the sheet.

Thanks in advance for your help!
Joe M.


--

Dave Peterson

Macro to hide sheets based upon cell value
 

Dave,

I created a macro with only the code you showed for #1. I got this error:
Object doesn't support this property or method (Error 438)
What did I do wrong?

Thanks,
Joe M.

"Dave Peterson" wrote:

#1.

Dim wks as worksheet
for each wks in activesheet.worksheets
if lcase(wks.range("a1").value) = lcase("hideme") then
wks.visible = xlsheethidden
end if
next wks

#2

Dim wks as worksheet
for each wks in activesheet.worksheets
'don't bother to see if it's visible or not
wks.visible = xlsheetvisible
next wks

#3.

Dim wks as worksheet
for each wks in activesheet.worksheets
if lcase(wks.name) like lcase("*somecommonstring*") then
wks.visible = xlsheethidden
end if
next wks

Remember that with #1 and #3, at least one sheet has to be visible.

Joe M. wrote:

I need help with 3 macros for hiding sheets:

1) I would like to hide several sheets based upon the value of a cell i.e.
A1 within the sheet to possibly be hidden.

2) I need a macro to unhide all hidden sheets. Some sheets are not hidden.

3) I need to hide certain sheets using the name of the sheet.

Thanks in advance for your help!
Joe M.


--

Dave Peterson
.

Macro to hide sheets based upon cell value
 

It was a typo (times 3).

Use:
For Each wks In ActiveWorkbook.Worksheets
not
For Each wks In ActiveSheet.Worksheets


Joe M. wrote:

Dave,

I created a macro with only the code you showed for #1. I got this error:
Object doesn't support this property or method (Error 438)
What did I do wrong?

Thanks,
Joe M.

"Dave Peterson" wrote:

#1.

Dim wks as worksheet
for each wks in activesheet.worksheets
if lcase(wks.range("a1").value) = lcase("hideme") then
wks.visible = xlsheethidden
end if
next wks

#2

Dim wks as worksheet
for each wks in activesheet.worksheets
'don't bother to see if it's visible or not
wks.visible = xlsheetvisible
next wks

#3.

Dim wks as worksheet
for each wks in activesheet.worksheets
if lcase(wks.name) like lcase("*somecommonstring*") then
wks.visible = xlsheethidden
end if
next wks

Remember that with #1 and #3, at least one sheet has to be visible.

Joe M. wrote:

I need help with 3 macros for hiding sheets:

1) I would like to hide several sheets based upon the value of a cell i.e.
A1 within the sheet to possibly be hidden.

2) I need a macro to unhide all hidden sheets. Some sheets are not hidden.

3) I need to hide certain sheets using the name of the sheet.

Thanks in advance for your help!
Joe M.


--

Dave Peterson
.


--

Dave Peterson