Filter Across Multiple Sheets

[gtomlinsonhicv]

I have a worksheet that has a summary page with filter drop down box (simple filter). The same data exists, and same filter box, across 5 additional sheets. I would like to select the filters on the summary page and have the selections flow across the remaining 5 pages.

The attachment shows 3 different filter boxes, there is actually 32 selections for the left most column. Again, I would like to be able to select 1-32, depending on the particulars that I need, and have that same selection flow to the other tabs.

Attachments​

• 
  Excelhelp1.JPG
  38.3 KB · Views: 0

 

[kevin9999]

Could you attach a copy of your actual sheet using the XL2BB - Excel Range to BBCode? I don't understand how your filter actually works as it appears to be on row 1,, but not possible to tell from an image. Alternatively, could you share your file using Dropbox, Google Drive or similar file sharing site.

 

[gtomlinsonhicv]

Could you attach a copy of your actual sheet using the XL2BB - Excel Range to BBCode? I don't understand how your filter actually works as it appears to be on row 1,, but not possible to tell from an image. Alternatively, could you share your file using Dropbox, Google Drive or similar file sharing site.

Thank you. My company does not allow me to have the add-in access so here's a dropbox link:

Bid Sheet - Working Version 2.0 DNU.xlsx

Shared with Dropbox

www.dropbox.com

Thank you for your help

 

[kevin9999]

As I understand it, you want to apply the filter to one sheet (currently called “Sheet1”) and have that filter applied automatically to all subsequent sheets – 6 in total. For this to happen automatically requires a Worksheet Code module located in the code module area of “Sheet1”. Simply changing a filter will not trigger code to run via Worksheet Change or Selection Change. In these cases, I usually use Worksheet Calculate event code. There again, Worksheet Calculate will not run merely on the change of an Autofilter, so the idea is to trick Excel into thinking a calculation has occurred. To achieve this, I’ve put a simple CountA() formula in cell D1 of “Sheet1”. Even though the value it returns doesn’t change, it tricks Excel into thinking a calculation has occurred – thereby triggering the code to run. You can move or hide this formula (white font or hide the column) but do not remove it entirely.

Once the code is triggered, the macro builds an array of values to use in the Autofilter of the remaining sheets. I was unable to test it fully because you have protected the following sheets: “Bid Standard ADA”, “Bid Signature”, “Bid Signature ADA”, and “Bid Other”. To run the code fully, you’ll need to unprotect those sheets (that can happen at code runtime) however, the code as it stands will demonstrate what’s possible on the sheets “Bid Summary” and “Bid Standard”.

Try it and get back to me if it works so far, and we can look at unprotecting those other sheets – or alternatively, protect them again and check the box “Use Autofilter” and then we can add the additional code to include them as well.

I’ve shared the file on Google Drive for you to test out – code is below.

Bid Sheet - Working Version 3.0 DNU.xlsm

Private Sub Worksheet_Calculate()
    On Error GoTo Escape
    Application.EnableEvents = False
    Dim nm As String, i As Long, arr() As Variant
    Dim r As Range, c As Range
    Set r = Range("A4:A500").SpecialCells(xlCellTypeVisible)
   
    'Build the filter criteria array
    For Each c In r
        If c.Value <> "" Then
            ReDim Preserve arr(i)
            arr(i) = CStr(Left(c.Value, 2))
            i = i + 1
        End If
    Next c
   
    'Loop through sheets and apply the filter
    For i = 1 To ThisWorkbook.Worksheets.Count
        nm = Worksheets(i).Name
        Select Case nm
            Case "Sheet1", "DATA"
            Case "Bid Summary"
                Worksheets(nm).Range("A44").AutoFilter 1, Array(arr), 7
            Case "Bid Standard"
                Worksheets(nm).Range("A10").AutoFilter 1, Array(arr), 7
            Case Else
                'Worksheets(nm).Range("A10").AutoFilter 1, Array(arr), 7
        End Select
    Next i
   
Continue:
    Application.EnableEvents = True
    Exit Sub
Escape:
    MsgBox "Error " & Err.Number & ": " & Err.Description
    Resume Continue
End Sub