Dialogue box to filter multiple sheets in a workbook

[alag76]

Hi,
Thanks in advance for your help. I have a workbook with multiple sheets (we'll call them A, B, C, and D). I would like to be able to create a shortcut/macro to filter A, B, C, and D for the same criteria that is in column A. I would also like a dialogue box to appear when I click on a keyboard shortcut where I can enter that criteria. Also, I'm not sure if this makes a difference or not but I run this set of reports every week so I would like for the macro to be available to every spreadsheet. I'm assuming if it's saved to my personal macros that this shouldn't be an issue. I really appreciate the help!

Kind regards,
Wendy

 

[alag76]

Just also wanted to mention I'm using Excel 2010. Thanks!

 

[alag76]

VBA to filter multiple sheets for the same criteria entered into a dialogue box

Hi Everyone,

I have multisheet workbook that I would like to filter a subset of the sheets by entering the filter criteria in a dialogue box. I have listed the names of each of the sheets below. In all the sheets, the column to be filtered is column A in all of the sheets.

Summary
Pages
Open Queries
Full SDV
ICF Pages SDV

Here is some code that I received previously but I think it will do all sheets and I only want it to do the sheets listed above. Thank in advance for your help!!

Sub LMP_Test()

    Dim wksSht                  As Worksheet
    Dim strFilterValue          As String
    
    Const lngColumnNoToFilter   As Long = 1 'Change this according to your filter column no
    
    On Error Resume Next
    strFilterValue = InputBox("Please provide filter value.", "Filter criteria", "")
    On Error GoTo -1: On Error GoTo 0: Err.Clear
    If LenB(Trim(strFilterValue)) Then
        For Each wksSht In ThisWorkbook.Worksheets
            With wksSht
                If .AutoFilterMode Then .AutoFilterMode = False
                With .UsedRange
                    .AutoFilter lngColumnNoToFilter, strFilterValue
                End With
            End With
        Next wksSht
    Else
        MsgBox "Either filter criteria not provided or aborted by user.", vbCritical + vbOKOnly
    End If
    
    Set wksSht = Nothing
    strFilterValue = vbNullString

End Sub

 

[jbeaucaire]

Re: VBA to filter multiple sheets for the same criteria entered into a dialogue box

Maybe this:

        For Each wksSht In ThisWorkbook.Sheets(Array("Summary", "Pages", "Open Queries", "Full SDV", "ICF Pages SDV"))

 

[alag76]

Re: VBA to filter multiple sheets for the same criteria entered into a dialogue box

Thanks for your response! I made the update but I get a runtime error '9': Subscript out of range

Also, is there to first clear all the filter on these pages before running the filter? I think that might cause some problems, no? Also, is there an issue of there are no results for one of the pages for that filter?

Sub LMP_Test()

Dim wksSht As Worksheet
Dim strFilterValue As String

Const lngColumnNoToFilter As Long = 1 'Change this according to your filter column no

On Error Resume Next
strFilterValue = InputBox("Please provide filter value.", "Filter criteria", "")
On Error GoTo -1: On Error GoTo 0: Err.Clear
If LenB(Trim(strFilterValue)) Then
For Each wksSht In ThisWorkbook.Sheets(Array("Summary", "Pages", "Open Queries", "Full SDV", "ICF Pages SDV"))
With wksSht
If .AutoFilterMode Then .AutoFilterMode = False
With .UsedRange
.AutoFilter lngColumnNoToFilter, strFilterValue
End With
End With
Next wksSht
Else
MsgBox "Either filter criteria not provided or aborted by user.", vbCritical + vbOKOnly
End If

Set wksSht = Nothing
strFilterValue = vbNullString

End Sub

 

[jbeaucaire]

Re: VBA to filter multiple sheets for the same criteria entered into a dialogue box

Subscript error 9 means that the line of code cannot find a named object. Since this is an array of worksheet names, I'll guess at least one of the names is not exactly correct. Perhaps a missing sheet or a hidden character in the spelling of one?

 

[alag76]

Re: VBA to filter multiple sheets for the same criteria entered into a dialogue box

I confirmed that all the names are correct and even when I only leave one spreadsheet in the code I get the same error.

 

[jbeaucaire]

Re: VBA to filter multiple sheets for the same criteria entered into a dialogue box

The syntax given is correct, it's a standard one. I just tested it on a workbook with 5 sheets and it cycles correctly unless I make even the tiniest error in sheetname, then I get that exact error.

Subscript error9 means a named object cannot be located.