Search code examples
excelvbafilterworksheettrend

Excel 2017. 7 worksheets, 1 filter to change them all

enter image description hereI have 7 worksheets which do exactly what I want. I am now being asked for a filter to show specific years. Done. However to look at a year of trend data, I have to manually filter each sheet.

I wouldn't mind going the extra mile, and if it's possible, have a filter in one of these sheets that organises the year in all the other sheets.

I have=YEAR(O9:O29148) on my largest sheet. A8:O8 and everything above is exactly the same on each sheet, every sheet has the same type of data in the same column. The only thing that does change is the unique data itself.

What I want is to have a Year filter (2000-2018) on my dashboard, which will then filter all the worksheets to show the same year, or all data if required.

Is this even possible?

(I do not understand VBA code, but I am capable of inserting it into VBA editor and then running said macro).

Any help would be greatly appreciated, thank you!

Solution 
  • Public Sub Filter_Sheets()

Dim i As Long
Dim comboBox As ControlFormat

With ThisWorkbook
    Set comboBox = .Worksheets(9).Shapes("Drop Down 229").ControlFormat
    For i = 1 To Worksheets.Count
        .Worksheets(i).UsedRange.AutoFilter Field:=15, Criteria1:=comboBox.List(comboBox.ListIndex)
    Next
End With

    End Sub

    This is the best fit I have managed to discover. I still get an error (AutoFilter method of Range class failed). However this does work. I am now using a combobox to change the auto filter on all 7 sheets as needed. In order to go back to select all, having "<>" in a cell the dropdown references, works to select all the data again.