Hello everyone.
I'm trying to filter a table based on a criteria and copy&paste the result to a different sheet.
Basically I have a huge amount of data stored in one sheet("Department ERP") and I need to filter column("GLO_MASS_LINE") based on a criteria, then copy&paste each of the results to a different sheet.
Since AutoFilter and subsequent copy&paste option is too slow, I decided to go for Advanced Filtering. I prepared a huge range of sheets (from sheets 11 to 38), where I want to put details for specific costs (for example, I want to filter the table stored in "Department ERP") for Employee education and copy&paste the result into sheet("EDUC") = sheet no. 11), then I want to filter "Events/Relationship marketing" and copy&paste the result to sheet("ERMA"), etc etc...)
So far, I have this code:
Sub GetData2()
Dim wbData As Range
Dim wbCriteria As Range
Dim wbExtract As Range
Dim i As Integer
Dim GLO2 As Integer
Application.ScreenUpdating = False
GLO2 = 21
i = 11
Set wbData = Worksheets("Department ERP").Range("A:P")
For GLO2 = 21 To 48
Worksheets("Inputs").Activate
Set wbCriteria = Worksheets("Inputs").Range(Cells(4, GLO2), Cells(5, GLO2))
Worksheets(i).Activate
wbData.CurrentRegion.AdvancedFilter Action:=xlFilterCopy, _
CriteriaRange:=wbCriteria, CopyToRange:=Worksheets(i).Range("A2"), Unique:=False
i = i + 1
Next GLO2
Application.ScreenUpdating = True
End Sub
The problem I am now facing is that the code loops through sheets and filters the data, but only for the first criteria (the criteria is still the first one "Employee education").
Would you help me find the problem here? Any help would be highly appreciated.
Thank you.
Bookmarks