Filtering multiple worksheets in excel based on the cell value

user_19723920 0
Viewed 63

I am using below code to filter all my worksheets based on the selections from the drop down list.

I am using inbuilt auto-filter function but somehow the code is not working.

The list I want to use to filter is in Sheet 2 and I have several worksheets

Any reason??

Sub apply_autofilter_across_worksheets()
Dim xWs As Worksheet
On Error Resume Next
For Each xWs In Worksheets
xWs.Range("A1").AutoFilter 3, Sheet2.Range("C4")
Next
End Sub
vba excel
1 Answers

A Worksheet Change: Dropdown AutoFilter

  • Copy the code into the sheet module of Sheet2.
  • The code runs automatically on each change of the dropdown value.
  • The code filters all worksheets except the one with the dropdown.
  • If you clear the dropdown cell, all filters will be cleared (hopefully you don't need to filter blanks).
  • Feel free to download the file from my Google drive.
Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    
    ' Define constants.
    ' Source
    Const sddCellAddress As String = "C4"
    ' Destination
    Const dFilterColumn As Long = 3
    Const dFirstCellAddress As String = "A1"
    
    ' Reference the source dropdown cell ('sddCell').
    Dim sddCell As Range: Set sddCell = Me.Range(sddCellAddress)
    
    ' Check if the value in the dropdown cell was not changed.
    If Intersect(sddCell, Target) Is Nothing Then Exit Sub
    
    ' Write the source worksheet's name ('sName') to a variable.
    Dim sName As String: sName = Me.Name
    
    ' Write the criterion converted to a string to a variable ('sCriterion').
    Dim sCriterion As String: sCriterion = CStr(sddCell.Value)
    
    ' Determine if the dropdown is not blank and write the information
    ' to a boolean variable ('DropDownIsNotBlank').
    ' This will be used to clear the filters when the dropdown is blank.
    Dim DropDownIsNotBlank As Boolean
    If Len(sCriterion) > 0 Then DropDownIsNotBlank = True
    
    ' Reference the workbook ('wb').
    Dim wb As Workbook: Set wb = Me.Parent ' (or 'Set wb = ThisWorkbook')
    
    ' Declare additional variables.
    Dim dws As Worksheet ' Destination Worksheet
    Dim drg As Range ' Destination Range
    
    ' Loop through all worksheets in the workbook...
    ' (Reference each worksheet in the workbook...)
    For Each dws In wb.Worksheets
        ' Check if the current worksheet is not the source worksheet.
        If dws.Name <> sName Then ' is not the source worksheet
            ' Clear any previous filters.
            If dws.FilterMode Then dws.ShowAllData
            ' Check if the dropdown is not blank
            If DropDownIsNotBlank Then
                ' Reference the destination range ('drg').
                Set drg = dws.Range(dFirstCellAddress).CurrentRegion
                ' Check if the destination range has enough columns.
                If drg.Columns.Count >= dFilterColumn Then ' enough columns
                    ' Apply the filter.
                    drg.AutoFilter dFilterColumn, sCriterion
                'Else ' not enough columns; do nothing
                End If
            'Else ' dropdown is blank (previous filters cleared); do nothing
            End If
        'Else ' is the source worksheet; do nothing
        End If
    Next dws
    
End Sub
user_98140690
Related