excelvbafilteriterationchallenge-response

Advanced filter fails at iterations, run-time error 1004


I'm iterating through columns(B through E) and trying to get unique values from each column to a cell in a different workbook. The iteration happens only once, doesn't get repeated(when x=3), and I'm getting run-time error 1004: application-defined or object-defined error

Dim LastCellRow As Integer
Dim inputRng As Range
Dim rng As Range

LastCellRow = Workbooks(MainFile).Sheets(1).Range("N3").End(xlDown).Row

For x=2 To 5
    wkbTemp.Worksheets("Specific").Range(Cells(2, x), Cells(2, x).End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:="", CopyToRange:=Workbooks(MainFile).Worksheets(1).Range("I4"), Unique:=True 

    Workbooks(MainFile).Sheets(1).Activate

    LastCell = Range("I5").End(xlDown).Address

    For Each rng In Range(Cells(5, 9), LastCell)
        If Cells(LastCellRow + 1, x + 12) = "" Then
            Cells(LastCellRow + 1, x + 12) = rng.Value
        Else
            Cells(LastCellRow + 1, x + 12) = Cells(LastCellRow + 1, x + 12) & "," & rng.Value
        End If
    Next
Next

After researching online, I have tried this option and and combinations of them. I also tried to make active the wkbTemp or MainFile before the iteration, but no luck.

With wkbTemp.Sheets("Specific")
        wkbTemp.Worksheets("Specific").Range(.Cells(2, x), .Cells(2, x).End(xlDown)).AdvancedFilter Action:=xlFilterCopy, CriteriaRange:="", CopyToRange:=Workbooks(MainFile).Worksheets(1).Range("I4"), Unique:=True
End With

Thank you!


Solution

  • Didn't solve completely but found the "why" of the problem. ...CopyToRange:=Workbooks(MainFile).Worksheets(1).Range("I4") One should not copy to the same range over and over. I made a new column for each iteration and it worked out perfectly. Maybe clearing the range after each iteration would work too; didn't try.