Apply filter row to multiple sheets

  • Hi Guys

    I have a workbook that has been split into numerous sheets according to specified column data via VBA

    However I need the 1st row of each sheet to have the filter drop down options available. I could go through each sheet individually, highlight the top row and click filter, but as I have about 30 sheets each with numerous columns that's rather time consuming.

    Can someone help me with the coding to do that please. I don't want to filter the data via vba, I just want each sheet to have the top row with the filter drop down buttons applied.


  • This assumes the data in each sheet starts in A1

    Sub AddFilter()
    Dim oWs As Worksheet
    For Each oWs In ThisWorkbook.Worksheets
    If Not oWs.AutoFilterMode Then oWs.Range("A1").AutoFilter
    Next oWs
    End Sub

    You need to prevent having to run this code in future by making sure that the new sheets have the filter added or, better still, convert the data to a Table which automatically has the drop downs and other features making data easier to work with.

  • Thanks, that would be fab too! This is the code being used to split the data by specified column

  • Without seeing an example workbook I would think that code could be much simpler.

    I've amended your code to add autofilter to the new sheet.

  • Pleased to help. The most efficient way to split data is to automate autofilter. That's how I would suggest doing it, but as I said you would need to attach a small example of your workbook.

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!