VBA to reapply filters on multiple worksheets

[starryeyed223]

I have a workbook with 10 worksheets. The "DATA" worksheet is where all data is entered/changes are made. The other 9 worksheets contain cell references to "DATA" with different filters applied. I'm trying to come up with some VBA code that will automatically reapply the filters on all 9 worksheets whenever "DATA" is updated.

So far, I've been able to find code that will automatically reapply the filter to one worksheet - I'm hoping to modify it to include the remaining 8 worksheets, but I don't know enough about VBA to accomplish that. I have the code below entered on the "DATA" worksheet and it's successfully reapplying the filter to the "VIOLATIONS" worksheet.

Private Sub Worksheet_Change(ByVal Target As Range)
With ActiveWorkbook.Worksheets("VIOLATIONS").ListObjects("Table2")
.AutoFilter.ApplyFilter
End With
End Sub

I'm hoping to get some help with modifying this code to reapply the filters on the following worksheets as well (table names in parentheses):
AR (Table3), CD (Table4), CH (Table5), JL (Table6), KK (Table7), KS (Table8), SK (Table9), TB (Table10)

 

[fadee2]

hi,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lst As ListObject
Dim wk As Worksheet
  For Each wk In ThisWorkbook.Worksheets
    For Each lst In wk.ListObjects
        lst.Range.AutoFilter
    Next lst
  Next wk
End Sub

This will activate Auto Filter on all of your worksheets once your worksheet changes. As for the application of the filter, each table fields and criteria will be required to automatically apply the filter.

hth...

 

[Fluff]

Maybe

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Ary As Variant
   Dim i As Long
   
   Ary = Array("Violations", "Table2", "AR", "Table3", "CD", "Table4")
   For i = 0 To UBound(Ary) Step 2
      With ActiveWorkbook.Worksheets(Ary(1)).ListObjects(Ary(i + 1))
         .AutoFilter.ApplyFilter
      End With
   Next i
End Sub

Just add the rest of the sheets & table names to the array

 

[starryeyed223]

hi,

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim lst As ListObject
Dim wk As Worksheet
  For Each wk In ThisWorkbook.Worksheets
    For Each lst In wk.ListObjects
        lst.Range.AutoFilter
    Next lst
  Next wk
End Sub

This will activate Auto Filter on all of your worksheets once your worksheet changes. As for the application of the filter, each table fields and criteria will be required to automatically apply the filter.

hth...

I think I did something wrong because I tried this and it removed the filters from all of the worksheets in my workbook. Could you explain what you mean by "each table fields and criteria will be required to automatically apply the filter"?

 

[Fluff]

I think I did something wrong because I tried this and it removed the filters from all of the worksheets in my workbook.

No, you did nothing wrong. That's exactly what the code does.

 

[starryeyed223]

Maybe

Private Sub Worksheet_Change(ByVal Target As Range)
   Dim Ary As Variant
   Dim i As Long
  
   Ary = Array("Violations", "Table2", "AR", "Table3", "CD", "Table4")
   For i = 0 To UBound(Ary) Step 2
      With ActiveWorkbook.Worksheets(Ary(1)).ListObjects(Ary(i + 1))
         .AutoFilter.ApplyFilter
      End With
   Next i
End Sub

Just add the rest of the sheets & table names to the array

Thank you! I added the rest of the worksheet/table names and tried the code. When I changed some of the data to test whether the filters would update, it gave me an error message saying "Run-time error '9': Subscript out of range". Screenshots of the error and the debug are attached. What did I do wrong?

 

[Fluff]

Oops, typo it should be

With ActiveWorkbook.Worksheets(Ary(i)).ListObjects(Ary(i + 1))

 

[starryeyed223]

Oops, typo it should be

With ActiveWorkbook.Worksheets(Ary(i)).ListObjects(Ary(i + 1))

That fixed it - THANK YOU!

 

[fadee2]

I think I did something wrong because I tried this and it removed the filters from all of the worksheets in my workbook. Could you explain what you mean by "each table fields and criteria will be required to automatically apply the filter"?

as @Fluff said, u did it right... and thats exactly what the code does, since i missed a flag, running it again would activate the autofilter. as for criteria and field i meant, which coloumns to filter on which criteria.

 

[Fluff]

That fixed it - THANK YOU!

Glad we could help & thanks for the feedback.