Took me all day to be able to post this on the forum. Yeesh.
You can optimize your code like the below. In VBA you usually don't need to select an item to work with it, or even have it visible.
Sub Update()
On Error GoTo ErrHandler
Sheets("Data").Range("$A$2:$BI$2500").AdvancedFilter Action:=xlFilterInPlace, Unique:=False
Sheets("Data").Range("$A$2:$BI$2500").AutoFilter
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Sheets("Sort").Range("B2:B2500").FillDown
Sheets("Sort").Range("I2:I2500").FillDown
Sheets("Data").Range("O2:O2500").FillDown
Application.Calculation = xlCalculationAutomatic
Sheets("PMS Interfaces").PivotTables("PivotTable1").RefreshTable
Sheets("PMS Interfaces").PivotTables("PivotTable2").RefreshTable
Sheets("PMS Interfaces").PivotTables("PivotTable3").RefreshTable
Application.ScreenUpdating = True
Exit Sub
ErrHandler:
MsgBox (Err.Number & ": " & Err.Description)
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub
To make your file size smaller, you can make all your pivot tables have the same Data Source. Pivot Tables, by default, store their data in the background. It is not just a reference. Pivot Tables are smart(ish), though and when two or more Pivot Tables share the same data source, it will only store the data once. If the sources are at all different, however, you are duplicating data. So, as a best practice, when possible, just grab the whole table for all your pivot tables and then only display the columns you need.
I don't know what else can be done to speed up the workbook.
Bookmarks