Hiding Sheets

[ArbiterWolf]

I have a excel workbook with about 60 sheets. I have a sheet that you input page numbers into and it separates the info onto the different sheets depending on the different info tied to particular page numbers. So I have a "Total" qty # of pages on every sheet. I would like to have all the sheets hidden until the total is >0.

So I currently have this code on the sheet you input the info on.

Sub Worksheet_Change(ByVal Target As Range)
Application.Volatile
If Sheet5.Range("C58").Value > 0 Then
Sheet5.Visible = True
Else
Sheet5.Visible = False
End If
End Sub

And it works exactly how I want it but what I want to know is if there is a easier way to apply this code to all the sheets? Or do I have to copy this code 60+ times for each individual sheet?
So the "total" cell is in the same cell for the first 20 sheets then in the same for the rest. 1-20 its C58 21-60 its E60.

 

[BSALV]

set this code in ThisWorkbook, in the 1st case, you give a list of all sheetnames, you don't want to do the check.

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

     Select Case Sh.Name                                        'look at the name of the changed sheet
          Case "summary", "details", "other sheet"              'sheets, you don't want to check
          Case Else                                             'all other sheets
               Application.Volatile
               Sh.Visible = (Sh.Range("C58").Value > 0)         'show if C58>0
     End Select
End Sub

 

[ArbiterWolf]

WOW thank you so much. That is much easier way to go about it.

Okay so I pasted the code into ThisWorkBook.

The sheets only disappear when I change something on that particular sheet. The Total number changing on the sheets is not triggering the code to make the changes. That's why I had the code originally on the Sheet you input the info on because as you input the page numbers its constantly triggering the code to update.

 

[BSALV]

Option Compare Text                                             'module not case sensitive

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

     If Sh.Name <> "Triggersheet" Then Exit Sub                 'not in the trigger-worksheet = end
     If Target.Address <> "$A$1" Then Exit Sub                  'not in the trigger cell = end

     For i = 1 To ThisWorkbook.Worksheets.Count                 'loop through all the worksheets
          With ThisWorkbook.Worksheets(i)                       'a worksheet
               Select Case .Name                                'look at the name of the sheet
                    Case "triggersheet", "details", "other sheet"     'sheets, you don't want to check
                    Case Else                                   'all other sheets
                         Application.Volatile
                         .Visible = (.Range("C58").Value > 0)   'show if C58>0
               End Select
          End With
     Next
End Sub

 

[ArbiterWolf]

Option Compare Text                                             'module not case sensitive

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

     If Sh.Name <> "Triggersheet" Then Exit Sub                 'not in the trigger-worksheet = end
     If Target.Address <> "$A$1" Then Exit Sub                  'not in the trigger cell = end

     For i = 1 To ThisWorkbook.Worksheets.Count                 'loop through all the worksheets
          With ThisWorkbook.Worksheets(i)                       'a worksheet
               Select Case .Name                                'look at the name of the sheet
                    Case "triggersheet", "details", "other sheet"     'sheets, you don't want to check
                    Case Else                                   'all other sheets
                         Application.Volatile
                         .Visible = (.Range("C58").Value > 0)   'show if C58>0
               End Select
          End With
     Next
End Sub

If Target.Address <> "$A$1" Then Exit Sub

What exactly is this line doing??

 

[ArbiterWolf]

Option Compare Text                                             'module not case sensitive

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)

     If Sh.Name <> "DATASheet" Then Exit Sub                 'not in the trigger-worksheet = end
     If Target.Address <> "$A$1" Then Exit Sub                  'not in the trigger cell = end

     For i = 1 To ThisWorkbook.Worksheets.Count                 'loop through all the worksheets
          With ThisWorkbook.Worksheets(i)                       'a worksheet
               Select Case .Name                                'look at the name of the sheet
                    Case "DATASheet", "Printing Check List", "Labels"      'sheets, you don't want to check
                    Case Else                                   'all other sheets
                         Application.Volatile
                         .Visible = (.Range("C58").Value > 0)   'show if C58>0
               End Select
          End With
     Next
End Sub

Im not to sure what to replace the $A$1 with.

 

[johnnyL]

It means, when coupled with the line above it that If A1 on the Sheet named "Triggersheet" was not the changed address then exit that sub.

 

[johnnyL]

@ArbiterWolf You should probably supply more info to assist you.

Examples:
What is the code that changes the other sheets Count# that you are wanting to check for?
It appears that you are wanting to check the codenames of sheets and not the sheet names?

 

[ArbiterWolf]

Well after removing the "If Target.Address <> "$A$1" Then Exit Sub" line of code it appears to be working perfectly.
Thank you so much!!!

I work for a company that prints and mails bills. So on the first sheet the user is entering page numbers example 1-1500 they then say if the 1-1500 needs Inserts,#9 envelopes, or if its multiple pages. When entering this info its splitting up the different groups of pages onto different sheets to then we print it the pages. So my goal was to only have the tabs/sheets appear that actually have info on them. At the moment I had made some code that turned the tabs black when there was info on them but I'm in the process of adding more inserts which will then triple my tab count so that wasn't going to work.

 

[johnnyL]

Well, if you are satisfied, that is what matters.