VBA Auto filter

[SavGDK]

I am trying to create an auto-filter on the first page of my workbook, the worksheet is named Index. I have created a dropdown box on this page with criteria that I want to filter against, on 8 different sheets in the workbook. I can't figure out how to reference the drop down cell on the Index page in cell I5 to be read then to go to each of the 8 different sheets and look at a specific column and filter the sheets based on the value in I5. Here is the code I have so far but it is only looking at one sheet and I don't think it is reading cell I5 on the Index page.

Thank you,

Sub Filter_Macro()
Application.ScreenUpdating = False

If [I5] = "ALL" Then
Worksheets("Index").AutoFilterMode = False
Else
Worksheets("Quality Data").AutoFilter Field:=[ao9].Column, Criterial1:=[I5]
For Each c In Worksheets("Quality Data").Range("A1:CZ400").Cells
c.AutoFilter Field:=c.Column, Visibledropdown:=False
Next
End If


End Sub

 

[JackDanIce]

Untested, however, try:

Sub Filter_Macro_v1()

    Dim w   As Worksheet
    Dim r   As Range
    Dim c   As Range
    
    Application.ScreenUpdating = False
    
    Set r = Sheets("Index").Range("15")
    
    If r.Value = "All" Then
        Sheets("Index").AutoFilterMode = False
    Else
        For Each ws In ThisWorkbook.Worksheets
            With ws
                If .Name <> "Index" Then
                    .AutoFilter field:=.Range("AO9").Column, Criteria1:=r.Value
                    For Each c In .Range("A1:CZ400")
                        c.AutoFilter field:=c.Column, visibledropdown:=False
                    Next c
                End If
            End With
        Next ws
    End If
    
    Application.ScreenUpdating = True
    
    Set r = Nothing
    
End Sub