Macro to extract data to specified sheets using advanved Filter

[howard]

I have data on Sheet "Imported Data in Cols A:V


I have branch code codes in Col V and have tried to write code to extract the data to sheets "BR 98150" ,"BR98151" "BR 98154" The criteria is in Z1:Z2 on these sheets

The 3 sheets are next to each other ("BR 98150" ,"BR98151" "BR 98154"

However when running the macro, I get a type Mismatch and the code below is highlighted

 Sheets("Imported Data").Range("A1:V1000").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Sheets(ws).Range("Z1:Z2"), CopyToRange:=Sheets(ws).Range("A1"), Unique:=False

See full code below

 Sub DataExtraction()

For Each ws In Sheets(Array("BR 98150", "BR 98151", "BR 98154"))
    With ws
    Sheets("Imported Data").Range("A1:V1000").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=Sheets(ws).Range("Z1:Z2"), CopyToRange:=Sheets(ws).Range("A1"), Unique:=False
      End With
      
    Next ws
Application.ScreenUpdating = True

  
End Sub

 

[Alex Blakenburg]

You might want to consider using the Dim statement, it will make this sort of error more obvious.

eg it would be Dim ws as Worksheet

If ws is already a worksheet then Sheets(ws) doesn't make sense, it would need to be Sheets(ws.Name) which means you should just use ws.
You have a With ws statement but are not using it.

Sub DataExtraction()

Dim ws As Worksheet

For Each ws In Sheets(Array("BR 98150", "BR 98151", "BR 98154"))

    ' Without using the With Statement
    Sheets("Imported Data").Range("A1:V1000").AdvancedFilter Action:=xlFilterCopy, _
        CriteriaRange:=ws.Range("Z1:Z2"), CopyToRange:=ws.Range("A1"), Unique:=False
    ' OR ALTERNATIVE
    ' Using the With Statement
    With ws
        Sheets("Imported Data").Range("A1:V1000").AdvancedFilter Action:=xlFilterCopy, _
            CriteriaRange:=.Range("Z1:Z2"), CopyToRange:=.Range("A1"), Unique:=False
    End With
  
Next ws
Application.ScreenUpdating = True

End Sub

 

[HaHoBe]

Hi Howard

another way would be to just use the array like

Array("BR 98150", "BR 98151", "BR 98154")

Sub DataExtraction_re()

Dim varString As Variant

Application.ScreenUpdating = False
For Each varString In Array("BR 98150", "BR 98151", "BR 98154")
    Sheets("Imported Data").Range("A1").CurrentRegion.AdvancedFilter _
            Action:=xlFilterCopy, _
            CriteriaRange:=Sheets(varString).Range("Z1:Z2"), _
            CopyToRange:=Sheets(varString).Range("A1"), _
            Unique:=False
   
Next varString
Application.ScreenUpdating = True

End Sub

Ciao,
Holger