Help! Filter all worksheets based on one column

[alexdrums123]

Hi all,

I have a workbook with multiple sheets (unfortunately due to vast amount of data I have to keep sheets separate).

The only unique column is the ID column in column A between sheets.

My goal is to be able to filter sheet 1 (the master sheet) based on columns B onwards: the IDs that are shown are then filtered in sheets 2 and 3 (even though they display different variables).

If anyone has any tips or solutions to this I would greatly appreciate it!!!

 

[mole999]

what is vast, you might be better off using SQL

 

[alexdrums123]

Unfortunately, under time pressure and won't have the time to jump on SQL!

 

[Peter_SSs]

Haven't tested this on a large data set but as far as I understand your question, I think this will do it.
Make sure the sheet name of your main sheet is amended in the code if necessary.
I have assumed that all sheets already have Autofilter set up on them and any applied AutoFilters can be removed (by the code) before applying the filters to match column A to the master sheet.
Also assumed that "#####" is not a valid ID on any sheet.

Test in a copy of your workbook.

Sub FilterAllSheets()
  Dim ws As Worksheet
  Dim aCrit As Variant
  Dim c As Range
  
  Const MasterSheetName As String = "Master" '<- Edit if required
  
  aCrit = "|######"
  For Each c In Sheets(MasterSheetName).UsedRange.Columns(1).SpecialCells(xlVisible)
    If c.Row > 1 And InStr(1, aCrit, "|" & c.Value & "|", vbTextCompare) = 0 Then
      aCrit = "|" & c.Value & aCrit
    End If
  Next c
  aCrit = Split(Mid(aCrit, 2, Len(aCrit) - 2), "|")
  For Each ws In Worksheets
    If ws.Name <> MasterSheetName Then
      With ws
        If .FilterMode Then .ShowAllData
        .AutoFilter.Range.AutoFilter Field:=1, Criteria1:=aCrit, Operator:=xlFilterValues
      End With
    End If
  Next ws
End Sub

 

[Fluff]

Cross Posted https://www.excelforum.com/excel-ge...worksheets-based-on-one-worksheet-column.html

While we do not prohibit Cross-Posting on this site, we do ask that you please mention you are doing so and provide links in each of the threads pointing to the other thread (see rule 13 here along with the explanation: Forum Rules).
This way, other members can see what has already been done in regards to a question, and do not waste time working on a question that may already be answered.