Loop filter multiple criteria different columns

[amitcohen]

Hello Team

I'm struggling with this one..

Step 1) Filter MAIN Category (On field 20)
Step 2) Then Filter SUB Category (On field 4)
Step 3) Do the magic
Step 4) Goto the next MAIN Category
Step 5) Filter SUB Category
Step 6) Do the magic again

Sub Filter_On_2_Columns_BB()
'This Macro Filter MAIN Category, Creat SUB Category list on AA Column, Then filter by the SUB Category
'On Error Resume Next
Range("X1").Value = "=COUNTA(V:V)+1"
Range("Y1").Value = "=COUNTA(C:C)"
Range("Z1").Value = "=COUNTA(AA:AA)"
Range("AB1").Value = "=SUBTOTAL(3,C:C)"


'Creat MAIN Category Column on V:V
    Columns("T:T").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Range("V1").Select
    ActiveSheet.Paste
'Remove Duplicated MAIN Categories From T:T onto 'V:V Column'
    Range(ActiveCell, ActiveCell.End(xlDown)).Select
    ActiveSheet.Range(Selection, Selection.End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
    Range("V1").Select
    Selection.ClearContents
    

    Range("A1:T1").Select
    Selection.AutoFilter

'Filter MAIN Category
For iMainCategory = 2 To Range("X1").Value
ActiveSheet.Range("A1:T" & Range("Y1").Value).AutoFilter Field:=20, Criteria1:=Range("V" & iMainCategory).Value
'Creat SUB Category on Column AA:AA
    Columns("D:D").Select
    Selection.SpecialCells(xlCellTypeVisible).Select
    Selection.Copy
    Range("AA1").Select
    ActiveSheet.Paste
'Remove Duplicated SUB Categories From T:T onto 'V:V Column'
    Range(ActiveCell, ActiveCell.End(xlDown)).Select
    ActiveSheet.Range(Selection, Selection.End(xlDown)).RemoveDuplicates Columns:=1, Header:=xlNo
    Range("AA1").Select
    Selection.ClearContents
    

'Filter SUB Category
For iSubCategory = 2 To (Range("Z1").Value + 1)
ActiveSheet.Range("A1:T" & Range("Y1").Value).AutoFilter Field:=4, Criteria1:=Range("AA" & iSubCategory).Value

'Do the Magic
'Based on the filter, now select the 1st VALUE in 'Column C' and copy it
    ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 3).Select
Selection.Copy
'After copy the 1st value on Column C, Now Goto the 'Parent' Column (A:A) and select the range available in the filter and paste the value to all cells in the range.
    Range("A2:A" & Range("Y1").Value).Select
    Selection.SpecialCells(xlCellTypeVisible).Select
'Paste Values
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
'Remove the value of the 1st product on Column A
ActiveSheet.AutoFilter.Range.Offset(1).SpecialCells(xlCellTypeVisible).Cells(1, 1).Select
Selection.ClearContents


Next iSubCategory
Next iMainCategory


End Sub

While it's running Ok on the first MAIN Category,
It fails to continue to the next one.


Can you help me spot the problem?

Many thanks,
Amit.