locked
Macro for Filter RRS feed

  • Question

  • Hi,

    I have one excel workbook which contains more than 10 sheets. Every sheet contains one column which is same across all the sheet. I want to write a macro in such a way that I can apply filter condition so that can get applied to all excel sheets.

    Is this is possible? 



    Aniruddha http://aniruddhathengadi.blogspot.com/

    Tuesday, April 16, 2013 10:52 AM

Answers

  • To Filter on Col E in each sheet and criteria in I1 in each sheet, try

    Sub TestFilterOn()
    For Each ws In ThisWorkbook.Worksheets
        ws.Range("E1").AutoFilter field:=1, Criteria1:=ws.Range("I1").Value
    Next ws
    End Sub

    Sub TestFilterOff()
    For Each ws In ThisWorkbook.Worksheets
        ws.Range("E1").AutoFilter
    Next ws
    End Sub


    Sheeloo

    • Marked as answer by Rex Zhang Thursday, April 25, 2013 8:41 AM
    Tuesday, April 16, 2013 1:00 PM

All replies

  • Do you want to apply filter to each sheet for the same column or do you want to apply filter in such a way that you can 'see' data from all sheets?

    Sheeloo

    Tuesday, April 16, 2013 11:34 AM
  • Hi,

    I want to apply filter to each sheet for the same column using Macro.


    Aniruddha http://aniruddhathengadi.blogspot.com/

    Tuesday, April 16, 2013 11:47 AM
  • To Filter on Col E in each sheet and criteria in I1 in each sheet, try

    Sub TestFilterOn()
    For Each ws In ThisWorkbook.Worksheets
        ws.Range("E1").AutoFilter field:=1, Criteria1:=ws.Range("I1").Value
    Next ws
    End Sub

    Sub TestFilterOff()
    For Each ws In ThisWorkbook.Worksheets
        ws.Range("E1").AutoFilter
    Next ws
    End Sub


    Sheeloo

    • Marked as answer by Rex Zhang Thursday, April 25, 2013 8:41 AM
    Tuesday, April 16, 2013 1:00 PM