Hiding rows in one tab based on cell value in another

[hoffmt28]

Ok-so someone else had supplied this code to me to hide rows in my tab named "Annual Record" based on a cell's value in another tab. The other tab's names are WO1, WO2, WO3, etc...

The code that I currently have is:
Private Sub Workbook_SheetChange(ByVal sh As Object, ByVal Target As Range)

Set A = Sheets("Annual Record")
Set b = Sheets("WO1")
Set c = Sheets("WO2")
Set d = Sheets("WO3")
Set e = Sheets("WO4")
Set WorkOrder1 = A.Rows("4:7")
Set WorkOrder2 = A.Rows("8:11")
Set WorkOrder3 = A.Rows("12:15")
Set WorkOrder4 = A.Rows("16:19")

If b.Range("AH5") = "Not Started" Then
WorkOrder1.Hidden = True
Else: WorkOrder1.Hidden = False
End If
If c.Range("AH5") = "Not Started" Then
WorkOrder2.Hidden = True
Else: WorkOrder2.Hidden = False
End If
If d.Range("AH5") = "Not Started" Then
WorkOrder3.Hidden = True
Else: WorkOrder3.Hidden = False
End If
If e.Range("AH5") = "Not Started" Then
WorkOrder4.Hidden = True
Else: WorkOrder4.Hidden = False
End If

End Sub

Now this person indicated that if I only had to add a few more tabs, as in WO4, WO5, etc...it wouldn't be a big deal (and I agree). However, I need to add a significant amount of additional tabs. So this person suggested adding a loop or something. If you need more information, let me know, but I think you can follow the trend, 4 rows are hidded on Annual Record for each tab basically.

Without blowing this code into a book, is there a quicker way to do this if I need to add a bunch more tabs???

[Tsjallie]

Take a look at the paragraph about WithEvents in this article: http://www.cpearson.com/excel/Events.aspx

BTW, please put the code you posted between code tags.

[Tsjallie]

Guess this is all you need.
Put it in ThisWorkbook.

Private Sub Workbook_NewSheet(ByVal Sh As Object)
    'Create a Named Range to refer to in Workbook_SheetChange procedure
    'Named Ranges refer to Workorder rows in the sheet Annual Report and are created upon inserting a new workorder sheet
    'Named Ranges must comply to the name convention: "AnRepWO" + Workorder number
    WoNum = 0
    For Each NamedRange In ActiveWorkbook.Names
        If Left(NamedRange.Name, 7) = "AnRepWO" Then
            WoNum = WorksheetFunction.Max(WoNum, Replace(NamedRange.Name, "AnRepWO", "")) 'select the highest existing workorder number
        End If
    Next NamedRange
    NextWoNum = WoNum + 1 'WoNum will be the highest existing workorder number
    Set NewRange = Worksheets("Annual Report").Range("AnRepWO" & WoNum).Offset(4, 0) 'Assuming there are always 4 rows for a workorder
    ActiveWorkbook.Names.Add Name:="AnRepWO" & NextWoNum, RefersTo:=NewRange 'Create new named range
End Sub
Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    
    If Not Intersect(Target, Sh.Range("A5")) Is Nothing Then 'Limit the changes to be processed
        'Use a named range to refer to the worksorder rows in the sheet Annual Report to be hidden or shown
        If LCase(Target.Value) = "not started" Then
            Range("AnRep" & Sh.Name).EntireRow.Hidden = True 'Hide the rows for the workorder represent by the sheet changed
        Else
            Range("AnRep" & Sh.Name).EntireRow.Hidden = False 'Show the rows for the workorder represent by the sheet changed
        End If
    End If
End Sub

This solution, however, requires you to name your sheets consistently (WO1, WO2, WO3 etc), but that shouldn't be a problem