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)

 

[joozfow]

Oops, typo it should be

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

Hi,

I've followed the instructions on this thread and I also encounter "runtime error '9'", even after correcting the typo - please see attached.

Is there anything else I should change to line 7 ?

J

 

[Fluff]

Check that all the sheet & table names are correct (including check for any leading/trailing spaces) & that the you have the correct table for each sheet.

 

[joozfow]

Check that all the sheet & table names are correct (including check for any leading/trailing spaces) & that the you have the correct table for each sheet.

Tab name was spelt incorrectly...Thank you for the quick response, it all works now!

 

[Fluff]

Glad to help & thanks for the feedback.