Auto Filter based on range on another sheet

Question

Hi,

I need to filter one sheet based on the values in a range on another sheet.

On sheet "ImpactReport" I need to filter Column A based on the values of Column A on the sheet "ActivePlants".  That range, which I've called PlantRange on ActivePlants will be dynamic.  If there are 3 values  say cells A1 to A3 in that range I need all 3 of those to show on the filtered list on the sheet "ImpactReport".

Not working code:

Sub FilterPlants()

Dim PlantRange As Range

Dim LastRw As Integer

Dim LastRwA As Integer

With Sheets("ActivePlants")

LastRw = .Cells(Rows.Count, "A").End(xlUp).Row

Set PlantRange = Range("A1:A" & LastRw)

End With

With Sheets("ImpactReport")

LastRwA = .Cells(Rows.Count, "A").End(xlUp).Row

Range("A1:K" & LastRwA).AutoFilter Field:=1, Criteria1:=PlantRange

End With

End Sub

Answer 1

Hi,

Try this code

Sub Macro1()

Dim source_rng As Range

Dim criteria_rng As Range

Set source_rng = Worksheets("Impact Report").Range("A1").CurrentRegion

Set criteria_rng = Worksheets("Activeplants").Range("A1").CurrentRegion

source_rng.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:=criteria_rng

End Sub

Please note that the headings in cell A1 of both sheets should be the same.

Hope this helps.

Answer 2

Could you create a stripped-down copy of the workbook (without sensitive information) and make it available through one of the websites that let you upload and share a file, such as Microsoft OneDrive (https://onedrive.live.com), FileDropper (http://filedropper.com) or DropBox (http://www.dropbox.com). Then post a link to the uploaded and shared file here.

Or register at www.eileenslounge.com (it's free) and post a message in the Excel forum. You can attach files up to 250 KB to a post there (zipped if necessary).

Answer 3

Thanks Hans!  When I update the code the filter returns no rows.  I've tried having just one row populated in the "PlantRange" as well as several rows populated.  I both cases the filtered sheet has all values unchecked in the filter.

Answer 4

You've missed a few . before items in the With ... End With blocks, you should use PlantRange.Value, and you should specify xlFilterValues as operator:

Sub FilterPlants2()

    Dim PlantRange As Range

    Dim LastRw As Long

    Dim LastRwA As Long

    With Worksheets("ActivePlants")

        LastRw = .Cells(.Rows.Count, "A").End(xlUp).Row

        Set PlantRange = .Range("A1:A" & LastRw)

    End With

    With Worksheets("ImpactReport")

        LastRwA = .Cells(.Rows.Count, "A").End(xlUp).Row

        .Range("A1:K" & LastRwA).AutoFilter Field:=1, Criteria1:=PlantRange.Value, _

            Operator:=xlFilterValues

    End With

End Sub