Repeat Macro for multiple Sheets - Next without For

Please use code tags with your code as per forum's rule.

Dim sheet_name As Range
 For Each sheet_name In Sheets("sheet3").Range("E:E")

Is looping through a range in sheet 3, as sheet_name is a range, not a sheet

You need

Dim sheet_name worksheet
 For Each sheet_name In Sheets

Thank you for your advise and sorry for my error.

I have changed the code as you suggested but I am still getting the error. I have added a line to my code below to show where the error is triggered. I have tried changing the order but get the same compile error.

 Dim sheet_name As Worksheet
  For Each sheet_name In Sheets("sheet3").Range("E:E")
    If sheet_name = "" Then
        Exit For
Else
    Sheets(sheet_name).Select
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A:$AH").AutoFilter Field:=5, Criteria1:="SBHS Yr 9"
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
 'check if data in worksheet after filter
    If IsEmpty(sheet_name) = True Then
    Selection.AutoFilter
    End If
    Next sheet_name
'***compile error - next without for***
Else
    If IsEmpty(sheet_name.Value) = False Then
    Selection.Copy
    Sheets("Year 9 Waitara").Select
    lMaxRows = Cells(Rows.Count, "a").End(xlUp).Row
    Range("a" & lMaxRows + 1).Select
    ActiveSheet.Paste
    Sheets(sheet_name.Value).Select
    Selection.AutoFilter
   End If
Next sheet_name
End Sub

It appears to me there are few errors on the code and still more errors to be found.

Sub test()
 Dim sheet_name As Worksheet
  For Each sheet_name In Sheets("sheet3").Range("E:E")
    If sheet_name = "" Then
        Exit For
Else
    Sheets(sheet_name).Select
    Rows("1:1").Select
    Selection.AutoFilter
    ActiveSheet.Range("$A:$AH").AutoFilter Field:=5, Criteria1:="SBHS Yr 9"
    Rows("2:2").Select
    Range(Selection, Selection.End(xlDown)).Select
    End If
 'check if data in worksheet after filter
    If IsEmpty(sheet_name) = True Then
    Selection.AutoFilter
    End If
    'Next sheet_name
'***compile error - next without for***

    If IsEmpty(sheet_name) = False Then
    Selection.Copy
    Sheets("Year 9 Waitara").Select
    lMaxRows = Cells(Rows.Count, "a").End(xlUp).Row
    Range("a" & lMaxRows + 1).Select
    ActiveSheet.Paste
    Sheets(sheet_name).Select
    Selection.AutoFilter
   End If
Next sheet_name
End Sub

This should give you some idea on how to loop through sheet collection

Sub test1()
 Dim sheet_name As Worksheet
  For Each sheet_name In ThisWorkbook.Worksheets
  
    With sheet_name
        If .Name = "" Then
            Exit For
        Else
            
            .Rows(1).AutoFilter
            .Range("$A:$AH").AutoFilter Field:=5, Criteria1:="SBHS Yr 9"
            .Rows("2:2").Select
            .Range(Selection, Selection.End(xlDown)).Select
            End If
         'check if data in worksheet after filter
            If IsEmpty(sheet_name) = True Then
            Selection.AutoFilter
            End If
            'Next sheet_name
        '***compile error - next without for***
        
            If IsEmpty(sheet_name) = False Then
            Selection.Copy
            Sheets("Year 9 Waitara").Select
            lMaxRows = Cells(Rows.Count, "a").End(xlUp).Row
            Range("a" & lMaxRows + 1).Select
            ActiveSheet.Paste
            Sheets(sheet_name).Select
            Selection.AutoFilter
           End If
    End With
        Next sheet_name
End Sub