To perform same task on worksheet (1), (2) and (3), not worksheet (4),(5) and (6)

[Mangosteenlu]

Hi All,
I only know a little about VBA, so can someone please help me to amend the below VBA code to perform the same task in worksheets (1), (2), and (3), not in worksheet (4), (5) and (6)?

Basically I just want to automatically filter column A with criteria of 'Overview' for worksheets (1) (2) and (3), the rest worksheet (4), (5), and (6) does not perform this task. so i wrote a simple VBA code 1 below, but it only works for the worksheet (1), not others. Also i need to take off the autofilter once the task is performed. (code 2 below does not seem to work) . Thank you for your help.

1. Sub Auto_Filter_with_Criteria_Overview ()
Range("A11:A182").AutoFilter field:=1, Criteria1:="Overview"
End Sub

2. Sub Take_off_Auto_Filter ()
Range("A11:A182").AutoFilterMode=false

 

[FormR]

Hi, you could give this a try:

Sub Auto_Filter_with_Criteria_Overview()
Dim i As Long
For i = 1 To 3
    Worksheets(i).Range("A11:A182").AutoFilter field:=1, Criteria1:="Overview"
Next i
End Sub

Sub Take_off_Auto_Filter()
Dim i As Long
For i = 1 To 3
    Worksheets(i).AutoFilterMode = False
Next i
End Sub

 

[Mangosteenlu]

thank you very much for the reply, yes it works.

but it does not work after I have changed worksheet name from sheet 1 to 'small tools' , from Sheet 2 to 'survey' and from sheet 3 to 'formwork' ,

Can you shine some light on this please?

 

[Cubist]

When you're using the index, it references the position of the sheet e.g. Worksheet(1) is the leftmost sheet. If you keep the same position and change the name, it would be fine, but it's still risky. Rather than using the index, use the actual name instead (even better if we use the code name instead of the display name). Note I've added error handling in case your sheet doesn't exist anymore. Not tested. Try on a copy.

Sub Auto_Filter_with_Criteria_Overview()
    Dim wsNames As Variant
    Dim wsName As Variant
    Dim ws As Worksheet
  
    wsNames = Array("small tools", "survey", "formwork")
  
    For Each wsName In wsNames
        On Error Resume Next
        Set ws = ThisWorkbook.Worksheets(wsName)
        On Error GoTo 0
      
        If Not ws Is Nothing Then
            ws.Range("A11:A182").AutoFilter Field:=1, Criteria1:="Overview"
        Else
            GoTo NextWorksheet
        End If
NextWorksheet:
    Next wsName
End Sub

Sub Take_off_Auto_Filter()
    Dim wsNames As Variant
    Dim wsName As Variant
    Dim ws As Worksheet
   
    wsNames = Array("small tools", "survey", "formwork")
   
    For Each wsName In wsNames
        On Error Resume Next
        Set ws = ThisWorkbook.Worksheets(wsName)
        On Error GoTo 0
       
        If Not ws Is Nothing Then
            ws.AutoFilterMode = False
        End If
    Next wsName
End Sub

 

[Mangosteenlu]

Hi Cubist, good morning. thank you very much for your help. much appreciated. It worked perfectly.