VBA - Cell references instead of hard coding

[itsgrady]

In the bold area below, I would like to make a cell references instead of hard coding. Is this possible to have about 10 cells VBA will reference instead of the actual criteria in the code? The criteria changes a lot more than what was first mentioned when the VBA code was built.

Sub SpecialDMann()

Dim ws As Worksheet

Set ws = ThisWorkbook.Sheets("VPI-All-WhsRpt")

Application.ScreenUpdating = False
If ws.AutoFilterMode Then
ws.AutoFilterMode = False

End If

Sheets("VPI-All-WhsRpt").Activate

ws.ListObjects("VPI_All_WhsRpt").AutoFilter.ShowAllData

ws.ListObjects("VPI_All_WhsRpt").Range.Select

ws.ListObjects("VPI_All_WhsRpt").Range.AutoFilter 22, Array("4A Back", "4B Back", "4C Back", "4D Back", "4A Middle", "4B Middle", "4C Middle"), Operator:=xlFilterValues

Set rng = ws.Range("A10")
rng.Select
rng.Application.Goto rng, True
Set rng = Nothing
Set ws = Nothing

Application.ScreenUpdating = True

End Sub

 

[Cubist]

Try something like this.

    Dim criteriaRange As Range
    Set criteriaRange = ThisWorkbook.Sheets("YourSheetName").Range("A1:A10") ' Update with your actual range
    ws.ListObjects("VPI_All_WhsRpt").Range.AutoFilter 22, criteriaRange, Operator:=xlFilterValues