VBA: filter multiple columns, then copy+paste

Question

I have tried unsuccessfully to filter multiple columns (of sheet data) and then copying a filtered column to another sheet (sheet2). Initial situation: The headers are in row 4:4

Filter column A by the string "SENR"
Copy the unique entries of column B into another sheet.

What have I achieved so far? I was able to implement point 2) with the following code:

Sub CreateUniqueList()
Dim LastRow As Long

Sheets("data").Select

LastRow = Cells(Rows.Count, "B").End(xlUp).Row

    ActiveSheet.Range("B5:B" & LastRow).AdvancedFilter _
    Action:=xlFilterCopy, _
    CopyToRange:=Sheets("sheet2").Range("A1"), _
    Unique:=True

End Sub

However, I don't manage filter column A first according to point 1). Does anyone here have an idea?

score 0 · Accepted Answer · edited Apr 06 '21 at 16:03

0

Sub CreateUniqueList()

    Dim LastRow As Long

    Sheets("data").Select

    LastRow = Cells(Rows.Count, "B").End(xlUp).Row

    ActiveSheet.Range("A:B").AutoFilter Field:=1, Criteria1:="SENR"

    Range("B5:B" & LastRow).Copy (Sheets("Sheet2").Range("A1"))

End Sub

edited Apr 06 '21 at 16:03

BigBen

46,229
7
24
40

answered Apr 06 '21 at 14:42

Brad Lee

187
1
10

thank you, before posting I tried ``` Worksheets("data").Range("A4").AutoFilter Field:=1, Criteria1:="SENR" ``` Due to a typo it didn't work. Your solution does :) thanks alot – Alexsom23814 Apr 06 '21 at 15:36
True, I only added the line with the criteria of "SENR" to my code above. – Alexsom23814 Apr 06 '21 at 15:41
1

Please note that good answers in the VBA tag space generally [avoid using Select](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba) where possible. – BigBen Apr 06 '21 at 16:03
@BigBen is correct, as usual. I kept the Select in here to focus only on the part Alexsom23814 was missing, but for future reference that is absolutely the best practice. – Brad Lee Apr 06 '21 at 19:08

score 0 · Answer 2 · answered Apr 06 '21 at 15:12

0

It doesn't know you want to filter by "SENR." You would need to add that value to a Criteria range and then call that out in your code.

answered Apr 06 '21 at 15:12

zombieHairdu

1
1

I added the following line ``` Worksheets("data").Range("A4").AutoFilter Field:=1, Criteria1:="SENR" ``` However, it sets the filter range in row 1:1 instead of 4:4 where the headers are. – Alexsom23814 Apr 06 '21 at 15:30
well NVM, i got a typo and that's why it didn't work – Alexsom23814 Apr 06 '21 at 15:35

VBA: filter multiple columns, then copy+paste

2 Answers2