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:
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").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
Would you help me find the problem here? Any help would be highly appreciated.
Thank you.
Bookmarks