Loop through a range and filter data based on the criteria in the columns cells

[abhay_547]

I have a worksheet named "Rawdata" and another worksheet named as "Mapping" and multiple other worksheets in a workbook. The Rawdata consists of some sales data which has 2 columns .i.e. States (e.g. Arizona, Albama, California etc) and another column consists of the cities from those states (e.g. Gilbert, Phoenix etc.), The multiple other worksheets which I have in the workbook are named after each state. The mapping tab consists of States column and City column in which City column consists multiple Cities entered against the each state separated by commas. Now I have used the below code to filter the Raw data worksheet based on the criteria which is entered in the Mapping tab column B .i.e. Cities. So the idea is to filter the data from Rawdata tab for each state and copy it into the respective state tab.

Sub FilterDataMacro()
Dim CopyShtname As String
CopyShtname = ThisWorkbook.Sheets("Mapping").Range("A2").Value
Dim FilterCriteria As Variant
FilterCriteria = Split(ThisWorkbook.Sheets("Mapping").Range("B2").Value, ",")

ActiveSheet.Range("$A$1:$U$10000").AutoFilter Field:=11, Criteria1:=FilterCriteria, Operator:=xlFilterValues
Selection.SpecialCells(xlCellTypeVisible).Select ' while selecting i want to exclude the header row
Selection.Copy
Sheets(CopyShtname).Select
Range("A1").Select
ActiveSheet.Paste
Application.CutCopyMode = False
End Sub

Is there a way I can run the above code in loop for a range of cells .i.e. Column A and B in mapping tab, where I have the Sheet name (in which the data needs to be pasted post filtering) in Column A and the Criteria in Column B (separated by Commas).