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!
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.