I am trying to speed up a pivot table filter of over 15000 items.
I have been advised to add the code line "pt.ManualUpdate=True" which is now preventing it from running at all - Runtime Error 91, Object variable or With block variable not set.
Has anyone got any suggestions of how to fix it but get the filter to run quickly, without any modification it takes around 4 mins to filter the report.
Sub openfilter()
'
' openfile Macro
'
Dim Pf As PivotField
Dim Pi As PivotItem
Dim pt As PivotTable
Sheets("Mile").Select
ActiveWindow.SmallScroll Down:=-21
ActiveWorkbook.ShowPivotTableFieldList = False
ActiveWindow.SmallScroll Down:=-270
Application.ScreenUpdating = False
'Application.Calculation = xlManual
'ActiveSheet.PivotTables("PivotTable3").ManualUpdate = True
'Application.DisplayAlerts = False
Set Pf = ActiveSheet.PivotTables("PivotTable3").PivotFields("Name")
'Fault on the next line...
pt.ManualUpdate = True
'Pf.ClearAllFilters
With Pf
For Each Pi In .PivotItems
If Not (Pi.Caption Like "Dave") Then
Pi.Visible = False
End If
Next Pi
End With
pt.ManualUpdate = False
Application.ScreenUpdating = True
End Sub
Bookmarks