Hide sheets by cell value
 

I am making a database in excel in which two types of information is
beeing produced. Yearly figures and year to date figures. Both type's
consist of multiple sheets (more that 20 is normal), with details
about certain markets. By a dropdown menu i would like to have a
selection on seeing only the sheets in producing the yearly figures or
the YTD figures.

I have the following working on one sheet, but I don't want to write
it for each sheet with a different name. I also noticed that when I
change the sheetname the macro quits working.

Dim sh As Worksheet
Set sh = Worksheets("Assumptions")
With Sheets("P&L current")
If sh.Range("Ae1").Value = 2 Then
.Visible = xlSheetVisible
ElseIf sh.Range("Ae1").Value = 1 Then
.Visible = xlSheetHidden
End If

The dropdown menu puts the value 1 or 2 in cell Ae1. is it possible
that a macro looks in every sheet for cell AE1 and hides or unhides it
by the value 1 or 2???

Hide sheets by cell value
 

To controll the visibility of each worksheet except Assumptions by looking at
cell AE1 of that sheet, use this code:
Sub ControlSheets()
Dim sh as Worksheet
for each sh in worksheets
if sh.Name < "Assumptions" then
If sh.Range("Ae1").Value = 2 Then
.Visible = xlSheetVisible
ElseIf sh.Range("Ae1").Value = 1 Then
.Visible = xlSheetHidden
End If
end if
Next
end Sub

--
Regards,
Tom Ogilvy

"A. Karatas" wrote:

I am making a database in excel in which two types of information is
beeing produced. Yearly figures and year to date figures. Both type's
consist of multiple sheets (more that 20 is normal), with details
about certain markets. By a dropdown menu i would like to have a
selection on seeing only the sheets in producing the yearly figures or
the YTD figures.

I have the following working on one sheet, but I don't want to write
it for each sheet with a different name. I also noticed that when I
change the sheetname the macro quits working.

Dim sh As Worksheet
Set sh = Worksheets("Assumptions")
With Sheets("P&L current")
If sh.Range("Ae1").Value = 2 Then
.Visible = xlSheetVisible
ElseIf sh.Range("Ae1").Value = 1 Then
.Visible = xlSheetHidden
End If

The dropdown menu puts the value 1 or 2 in cell Ae1. is it possible
that a macro looks in every sheet for cell AE1 and hides or unhides it
by the value 1 or 2???

Hide sheets by cell value
 

hello Tom,

for the folowwing code i get the following error

.Visible = xlSheetVisible


= invalid or unqualified error????

Hide sheets by cell value
 

try putting sh before the . visible on the 2 lines where it appears.

--


Gary


"A. Karatas" wrote in message
oups.com...
hello Tom,

for the folowwing code i get the following error

.Visible = xlSheetVisible


= invalid or unqualified error????

Hide sheets by cell value
 

I copied your code - guess I didn't clean it up enough.

Sub ControlSheets()
Dim sh as Worksheet
for each sh in worksheets
if sh.Name < "Assumptions" then
If sh.Range("Ae1").Value = 2 Then
sh.Visible = xlSheetVisible
ElseIf sh.Range("Ae1").Value = 1 Then
sh.Visible = xlSheetHidden
End If
end if
Next
end Sub

Regards,
Tom Ogilvy

"A. Karatas" wrote:

hello Tom,

for the folowwing code i get the following error

.Visible = xlSheetVisible


= invalid or unqualified error????

Hide sheets by cell value
 

A. Karatas wrote:

I am making a database in excel in which two types of information is
beeing produced. Yearly figures and year to date figures. Both type's
consist of multiple sheets (more that 20 is normal), with details
about certain markets. By a dropdown menu i would like to have a
selection on seeing only the sheets in producing the yearly figures or
the YTD figures.

I have the following working on one sheet, but I don't want to write
it for each sheet with a different name. I also noticed that when I
change the sheetname the macro quits working.

Dim sh As Worksheet
Set sh = Worksheets("Assumptions")
With Sheets("P&L current")
If sh.Range("Ae1").Value = 2 Then
.Visible = xlSheetVisible
ElseIf sh.Range("Ae1").Value = 1 Then
.Visible = xlSheetHidden
End If

The dropdown menu puts the value 1 or 2 in cell Ae1. is it possible
that a macro looks in every sheet for cell AE1 and hides or unhides it
by the value 1 or 2???

Is there something on each worksheet that defines it as ytd or yearly other
than the "1" or "2" that you manually allocate? Or are you manually
inputting "1" or "2" on every worksheet to determine that?

This will make all the workshseets with 2 in A1 visible and all the
worksheets with 1 in A1 invisible (and will ignore sheets with neither 2
nor 1 in A1):

Sub test()
For Each ws In Worksheets 'looks through the whole collection of worksheets
If ws.Range("A1") = 2 Then
ws.Visible = False
ElseIf ws.Range("A1") = 1 Then
ws.Visible = True
End If
Next ws
End Sub

HTH
--
Damien

Hide sheets by cell value
 

On 20 mrt, 15:27, Tom Ogilvy
wrote:
I copied your code - guess I didn't clean it up enough.

Sub ControlSheets()
Dim sh as Worksheet
for each sh in worksheets
if sh.Name < "Assumptions" then
If sh.Range("Ae1").Value = 2 Then
sh.Visible = xlSheetVisible
ElseIf sh.Range("Ae1").Value = 1 Then
sh.Visible = xlSheetHidden
End If
end if
Next
end Sub

Regards,
Tom Ogilvy



"A. Karatas" wrote:
hello Tom,

for the folowwing code i get the following error

.Visible = xlSheetVisible

= invalid or unqualified error????- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

Thankx guys, it works great

Hide sheets by cell value
 

On 20 mrt, 15:34, Damien McBain wrote:
A. Karataswrote:
I am making a database in excel in which two types of information is
beeing produced. Yearly figures and year to date figures. Both type's
consist of multiple sheets (more that 20 is normal), with details
about certain markets. By a dropdown menu i would like to have a
selection on seeing only the sheets in producing the yearly figures or
the YTD figures.

I have the following working on one sheet, but I don't want to write
it for each sheet with a different name. I also noticed that when I
change the sheetname the macro quits working.

Dim sh As Worksheet
Set sh = Worksheets("Assumptions")
With Sheets("P&L current")
If sh.Range("Ae1").Value = 2 Then
.Visible = xlSheetVisible
ElseIf sh.Range("Ae1").Value = 1 Then
.Visible = xlSheetHidden
End If

The dropdown menu puts the value 1 or 2 in cell Ae1. is it possible
that a macro looks in every sheet for cell AE1 and hides or unhides it
by the value 1 or 2???

Is there something on each worksheet that defines it as ytd or yearly other
than the "1" or "2" that you manually allocate? Or are you manually
inputting "1" or "2" on every worksheet to determine that?

This will make all the workshseets with 2 in A1 visible and all the
worksheets with 1 in A1 invisible (and will ignore sheets with neither 2
nor 1 in A1):

Sub test()
For Each ws In Worksheets 'looks through the whole collection of worksheets
If ws.Range("A1") = 2 Then
ws.Visible = False
ElseIf ws.Range("A1") = 1 Then
ws.Visible = True
End If
Next ws
End Sub

HTH
--
Damien- Tekst uit oorspronkelijk bericht niet weergeven -

- Tekst uit oorspronkelijk bericht weergeven -

The dropdown box can make 2 selections. Year which is 1 and YTD which
is 2

A formula on the sheets for year figures searches for the value on the
sheet assumption which says = if(assumptions!AE1=2;1;2) and the
figures YTD looks only at AE1.

In this way it is working super