Using vba to autofilter one column, with values from a different column on a different sheet

Question

I need the VBA to take the values from column A in the "Final_Sheet" and filter by those values in column A of the "SIS_Case_Contacts" sheet.

Sub filter()
Dim lastRowSISCaseContacts As Long
Dim lastRowFinalSheet As Long
Dim ws As Worksheet
Set ws = Sheets("SIS_Case_Contacts")

With Sheets("SIS_Case_Contacts")
    lastRowSISCaseContacts = .Range("A" & .Rows.Count).End(xlUp).Row
End With

With Sheets("Final_Sheet")
    lastRowFinalSheet = .Range("A" & .Rows.Count).End(xlUp).Row
End With

    ws.Range("A2:A" & lastRowSISCaseContacts).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
    Sheets("Final_Sheet").Range("A2:A" & lastRowFinalSheet), Unique:=False
End Sub

This gives me a Run-time error '1004': Advancedfilter method of Range class failed.

If I wanted cell A2 and anything that is below, what would be the best way to do that. — Jay, Apr 13 '20 at 20:34
[Find the last row](https://stackoverflow.com/questions/11169445/error-in-finding-last-used-cell-in-excel-with-vba) and reference it. — BigBen, Apr 13 '20 at 20:38
Okay fixed that, thank you, now the error still occurs though. — Jay, Apr 13 '20 at 20:41
Please read that question carefully and implement the answer. You have to actually find the last row. — BigBen, Apr 13 '20 at 20:42
The last row in column "A" will be changing based on the filter applied — Jay, Apr 13 '20 at 20:45

score 0 · Answer 1 · answered Apr 13 '20 at 20:55

0

AdvancedFilter rather than AutoFilter will allow you to use the values from one sheet as the criteria to filter another sheet with.

Something like this could work:

    Dim lastRowContacts As Long
    Dim lastRowFinalSheet As Long

    With Sheets("Contacts")
        lastRowContacts = .Range("A" & .Rows.Count).End(xlUp).Row
    End With

    With Sheets("Final_Sheet")
        lastRowFinalSheet = .Range("A" & .Rows.Count).End(xlUp).Row
    End With

    ws.Range("A2:A" & lastRowContacts).AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
            Sheets("Final_Sheet").Range("A2:A" & lastRowFinalSheet), Unique:=False

answered Apr 13 '20 at 20:55

Matt

38
6

This looks really good, do you know what could be happening when I run this and it gives me a Run-time error '424'? – Jay Apr 13 '20 at 21:00
It means that an object referred to in the code wasn't found. You will still need the `Dim ws...` and `Set ws...` lines of your code. If you have already included those, are the sheet names correct? – Matt Apr 13 '20 at 21:04
I updated the my original code question above. And am now getting the rang of object error. – Jay Apr 15 '20 at 13:18

Using vba to autofilter one column, with values from a different column on a different sheet

1 Answers1