Excel filter on another sheet dependent upin cell value

You will have to do something with VBA to have this work. something to try would be this:

Sub FilterSheet()
    Dim wsMain As Worksheet, wsFilter As Worksheet
    Dim wbMain As Workbook
    Dim Filter, LastRow
    Set wbMain = ActiveWorkbook
    Set wsMain = wbMain.Sheets(1)
    Set wsFilter = wbMain.Sheets(2)
    
    LastRow = wsMain.Range("A" & Rows.Count).End(xlUp).Row
    
    For Each c In wsFilter(wsFilter.Range("A1"), wsFilter.Range("A" & Rows.Count).End(xlUp))
        If c.Value = 1 Then
            Filter = wsFilter.Range("B" & c.Row).Value
            wsMain.Range("A1").Select
            Selection.AutoFilter
            wsMain.Range("A1:B" & LastRow).AutoFilter Field:=1, Criteria1:=Filter
        End If
    Next
End Sub

Select allOpen in new window

I did have to add headings to the "First Sheet" so that the auto filter would be able to filter all the values since it keeps the first row as headers.

Hi Mike

This throws a run time error 438 - Object does support this property or method when starting the loop. Just out of interest, is this vb only interrogating the first sheet? The filter on the first sheet will need to be driven by the cell entry on the second sheet.

I see, I typed too fast and missed a ".Range" in the loop it should be like this:

Sub FilterSheet()
    Dim wsMain As Worksheet, wsFilter As Worksheet
    Dim wbMain As Workbook
    Dim Filter, LastRow, c
    Set wbMain = ActiveWorkbook
    Set wsMain = wbMain.Sheets(1)
    Set wsFilter = wbMain.Sheets(2)
    
    LastRow = wsMain.Range("A" & Rows.Count).End(xlUp).Row
    
    For Each c In wsFilter.Range(wsFilter.Range("A1"), wsFilter.Range("A" & Rows.Count).End(xlUp))
        If c.Value = 1 Then
            Filter = wsFilter.Range("B" & c.Row).Value
            wsMain.Range("A1").Select
            Selection.AutoFilter
            wsMain.Range("A1:B" & LastRow).AutoFilter Field:=1, Criteria1:=Filter
        End If
    Next
End Sub

Select allOpen in new window

Have you tried Slicers? The slicer will initially be placed on the source sheet but it can be moved to another sheet with Cut and Paste. This will then show a list of "buttons" for each unique entry from the list.

Do you want the cursor to end up back on the Data sheet or the Link Sheet?

I also just noticed I didn't activate the "First Sheet" before trying to select a cell on it. You will have to add

wsMain.Activate

Select allOpen in new window

right before the line with:

wsMain.Range("A1").Select

Select allOpen in new window

So that the whole thing will look like this:

Sub FilterSheet()
    Dim wsMain As Worksheet, wsFilter As Worksheet
    Dim wbMain As Workbook
    Dim Filter, LastRow, c
    Set wbMain = ActiveWorkbook
    Set wsMain = wbMain.Sheets(1)
    Set wsFilter = wbMain.Sheets(2)
    
    LastRow = wsMain.Range("A" & Rows.Count).End(xlUp).Row
    
    For Each c In wsFilter.Range(wsFilter.Range("A1"), wsFilter.Range("A" & Rows.Count).End(xlUp))
        If c.Value = 1 Then
            Filter = wsFilter.Range("B" & c.Row).Value
            wsMain.Activate
            wsMain.Range("A1").Select
            Selection.AutoFilter
            wsMain.Range("A1:B" & LastRow).AutoFilter Field:=1, Criteria1:=Filter
            
        End If
    Next
End Sub

Select allOpen in new window

Hi Mike

Thanks for the above. It isn't doing anything (as far as I can see), but it doesn't error now :)

Hi Rob

Never heard of a slicer! I will have a delve. It doesn't matter too much where the cursor position ends as this spreadsheet is going to be for referencing (so to'ing and fro'ing between sheets)

Hi Rob

I have just realised slicers are pivots. Yes, I think these would be excellent for the requirement. But the person who will be using this data doesn't like pivots, so I'm really trying to use filters to hone in, in a similar but much, much less sophisticated way.

Slicers don't have to involve a pivot table. I am working on something currently which involves slicers without pivots.

Just realised what whoever told you about Pivots may be referring to. Slicers do need the data to be held in a Table rather than a standard list.

Technically there is very little difference, the data still has a header row and is held as one record per row below that. Excel just recognises the data as being related in a table and will do extra things with it.

See attached, I have converted the data to a table and included a slicer on the Filter sheet. I created it on the data sheet but then used Cut and Paste to move it.

Thanks
Rob H
Copy-of-sample-with-slicer.xlsx

Rob

That is perfect, just what I need. Thanks to both of you for your help.

Diane

Hi Rob.

Yes, thank you it's all in place and user is very happy. Thanks again for your help.