Advanced filter fails at iterations, run-time error 1004

Question

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!

score 0 · Accepted Answer · answered Jul 13 '15 at 00:59

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.

score 0 · Answer 2 · answered Aug 16 '17 at 05:27

I had a similar issue where I received error 1004 when using the AdvancedFilter method, and copying to a different range. It turned out the range that I was copying to had a header in the first row, so maybe overwriting data caused this in my case. I removed the header from the first row, and the vba worked. Then I put the header back, after it worked once without the header, and the same VBA code worked with the header. My CopyToRange is an entire column (column H) because I don't know how many rows will be needed since i'm filtering on unique records.

Here is what worked (writeRow is an Integer):

ThisWorkbook.Worksheets("Calculations").Range("D1:D" & writeRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ThisWorkbook.Worksheets("Calculations").Columns(8), Unique:=True

I also was able to change to CopyToRange to a different sheet than the range for the advanced filter and it worked too!

Here is code to Copy to a different sheet: ThisWorkbook.Worksheets("Calculations").Range("D1:D" & writeRow).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=ThisWorkbook.Worksheets("Sheet1").Columns(8), Unique:=True

Advanced filter fails at iterations, run-time error 1004

2 Answers2