locked
Apply Auto Filter on Multiple Worksheets Except One RRS feed

  • Question

  • Hello,

    It would be greatly appreciated if someone could start me off on the right direction to write a VBA which will auto filter multiple worksheets in my workbook (all worksheets contain same table structure), using the as a trigger the active worksheet's filtered field and excluding in the auto filter a worksheet that does not contain table data.

    Thank you in advance.

    Thursday, March 31, 2016 8:05 PM

Answers

  • Hi Amaria,

    >> which will auto filter multiple worksheets in my workbook (all worksheets contain same table structure), using the as a trigger the active worksheet's filtered field and excluding in the auto filter a worksheet that does not contain table data.

    You could traversal every worksheet, if the worksheet does not need to filter, jump over it, and auto filter other worksheets. Here is a simple code, you could modify it to your own requirement.

    Sub AutoFilter()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Sheet3" Then
          ws.Cells.AutoFilter      
        End If
    Next ws
    End Sub

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, April 1, 2016 2:36 AM
  • Edward Z's code is cool, i.e. For each -- in.
    So I've modified my code.
    Private Sub btn_AutoFilter_Click()
        Dim wSheet As Worksheet
        Dim valueRng As Range
        ' ---
        Application.ScreenUpdating = False
        ' ---
        For Each wSheet In ThisWorkbook.Worksheets
            wSheet.Activate
            Set valueRng = ActiveSheet.Range("C3:C12")
            If Application.WorksheetFunction.Sum(valueRng) > 0 Then
                ActiveSheet.Range("B2").AutoFilter field:=1
            End If
        Next
        ' ---
        Worksheets(1).Activate
        Application.ScreenUpdating = True
    End Sub
    

    Friday, April 1, 2016 3:10 AM

All replies

  • Hi Amaria,

    >> which will auto filter multiple worksheets in my workbook (all worksheets contain same table structure), using the as a trigger the active worksheet's filtered field and excluding in the auto filter a worksheet that does not contain table data.

    You could traversal every worksheet, if the worksheet does not need to filter, jump over it, and auto filter other worksheets. Here is a simple code, you could modify it to your own requirement.

    Sub AutoFilter()
    Dim ws As Worksheet
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Sheet3" Then
          ws.Cells.AutoFilter      
        End If
    Next ws
    End Sub

    Best Regards,

    Edward


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.


    Friday, April 1, 2016 2:36 AM
  • Hi,

    I've tries.

    Private Sub btn_AutoFilter_Click()
        Dim sheetCnt As Integer
        Dim idx As Integer
        Dim valueRng As Range
        ' ---
        sheetCnt = ThisWorkbook.Sheets.Count
        Application.ScreenUpdating = False
        ' ---
        For idx = 1 To sheetCnt
            Worksheets(idx).Activate
            Set valueRng = ActiveSheet.Range("C3:C12")
            If Application.WorksheetFunction.Sum(valueRng) > 0 Then
                ActiveSheet.Range("B2").AutoFilter field:=1
            End If
        Next
        ' ---
        Worksheets(1).Activate
        Application.ScreenUpdating = True
    End Sub 
    The above sample:
    Range("C3:C12") is used for judging whether a worksheet contains data or not.
    Please modify Range as your need.

    Regards.
    Friday, April 1, 2016 2:58 AM
  • Edward Z's code is cool, i.e. For each -- in.
    So I've modified my code.
    Private Sub btn_AutoFilter_Click()
        Dim wSheet As Worksheet
        Dim valueRng As Range
        ' ---
        Application.ScreenUpdating = False
        ' ---
        For Each wSheet In ThisWorkbook.Worksheets
            wSheet.Activate
            Set valueRng = ActiveSheet.Range("C3:C12")
            If Application.WorksheetFunction.Sum(valueRng) > 0 Then
                ActiveSheet.Range("B2").AutoFilter field:=1
            End If
        Next
        ' ---
        Worksheets(1).Activate
        Application.ScreenUpdating = True
    End Sub
    

    Friday, April 1, 2016 3:10 AM