Macro (using a Filter) Question and help

[oldmaninla]

I have the following macro.
Originally, the Filter Button in Excel was in column AA but I couldn't make it work so I moved the Filter to Column A (1).
If I only want to have the filter button in Column AA how can I make my macro work?
I tried changing the AutoFilter Field:=27 but that doesn't work.
Any ideas? Thx



Sub ShowOnly_1()
'
' ShowOnly_1 Macro
'

'
Dim ws As Worksheet

For Each ws In Worksheets

'Range("AA5").Select
'ActiveSheet.Range("$AA$5:$AA$618").AutoFilter Field:=1, Criteria1:="show"
If ws.Visible = True Then
If ws.Name <> "Input" then
If ws.Name <> "Control" then
ws.Activate
ActiveSheet.Range("$A$5:$A$618").AutoFilter Field:=1, Criteria1:="show"

End If
End If
End If
Next

End Sub

 

[dave3009]

If you want to set only AA as the filter then use AA but it's field 1, as in the first column in the range

Option Explicit

Sub ShowOnly_1()
Dim ws As Worksheet                                                                 ' Variable to store the worksheet


For Each ws In ThisWorkbook.Worksheets                                              ' Loop over all the worksheets
    
    With ws                                                                         ' use With to save repeating the variable
        
        If .Visible = True Then                                                     ' test if the sheet is visible
            
            If InStrRev("|Input|Control|", "|" & .Name & "|") = 0 Then              ' Test the name is one we want to use
                
                .Activate                                                           ' activate the sheet - this is unnecessary
                
                .Range("$AA$5:$AA$618").AutoFilter Field:=1, Criteria1:="show"      ' Filter on column AA if the value is Show
            
            End If                                                                  ' End the test for sheet name
        
        End If                                                                      ' end the visibility test
    
    End With                                                                        ' end the use of the sheet

Next ws                                                                             ' move to the next sheet

End Sub

 

[oldmaninla]

Thx Dave3009, but for some reason, it still wants to Filter on Column 1

 

[dave3009]

Is row 5 formula driven?

 

[oldmaninla]

Cell AA5 contains just a string with the word "filter"

 

[dave3009]

OK, I did not ask about cell AA5, I asked about row 5. Are there any formulas in row 5, especially cell A5?

 

[oldmaninla]

There is a formula in one cell in row 5 but not in A5

 

[oldmaninla]

Hi Dave3009- I moved the one formula in row 5, cleared the Filter that was still on A5 and now it is working fine. Thank you for your direction!