Simplying Excel VBA code

Question

I have a workbook that creates data reports for individual clients, with each client having a specific worksheet in the workbook. The raw data is stored in a sheet RawData. On each client specific worksheet, the raw data is duplicated e.g. A129 of client worksheet says =RawData!A1 for all relevant rows and columns.

In each of the client worksheets the raw data is then autofiltered by the client name (T51 in below code) and the month in question (T52). This is maintained by a macro (below) which is on every single client worksheet. The macro runs every time a cell is updated. Hence, once the raw data is updated it flows through automatically into the relevant client report.

While this works wonderfully in populating only the relevant client's specific data in their report; due to there being +20 client reports, it runs slowly. After making a change to the raw data or the month needed it takes 25 mins to run through and update all the autofilters in all the spreadsheets.

If anyone can assist, I would like to be able to put the code on one worksheet (say, the RawData sheet) which then loops through all the sheets and updates the autofilters that way. My guess is that doing so would make the whole process faster - by having one code run through 20 sheets successively rather than 20 trying to run at once.

Any help or any ideas of how it could be done better would be much appreciated. Thank you.

Private Sub Worksheet_Calculate()

    If Me.FilterMode = True Then
        With Application
            .EnableEvents = False
            .ScreenUpdating = False
        End With

        With ActiveWorkbook
            .CustomViews.Add ViewName:="Mine", RowColSettings:=True
            Me.AutoFilterMode = False
            .CustomViews("Mine").Show
            .CustomViews("Mine").Delete
        End With

        With ActiveWorkbook
        Range("$A$129:$I$33602").AutoFilter Field:=2, Criteria1:=Range("T51").Value
        Range("$A$129:$I$33602").AutoFilter Field:=5, Criteria1:=Range("T52").Value
        End With

        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With

    End If

End Sub

Further information: T51 and T52 are controlled by slicers linked to pivot chart. For example, changing the Month slicer changes all pivot tables in all client worksheets along with T52 which then updates the filters. Same goes for T51 and client names, although that's only worksheet specific

Answer 1

Loop through worksheets:

Sub ertdfgcvb()
Dim ws As Worksheet

With Application
    .EnableEvents = False
    .ScreenUpdating = False
End With

For Each ws In ActiveWorkbook.Worksheets

    If ws.FilterMode = True Then

        With ActiveWorkbook 'I don't know what this view does, you might need to select the sheet beforehand
            .CustomViews.Add ViewName:="Mine", RowColSettings:=True
            ws.AutoFilterMode = False
            .CustomViews("Mine").Show
            .CustomViews("Mine").Delete
        End With

            ws.Range("$A$129:$I$33602").AutoFilter Field:=2, Criteria1:=ws.Range("T51").Value
            ws.Range("$A$129:$I$33602").AutoFilter Field:=5, Criteria1:=ws.Range("T52").Value
    End If
Next ws

With Application
        .EnableEvents = True
        .ScreenUpdating = True
End With

End Sub