Pivot table refresh slow

I have a macro that I am using to refresh all the pivot tables in a workbook.  I have  10 pivot tables in the workbook that I am refreshing.  I am using the following code and it takes a really long time to run.

Sub AllWorkbookPivots()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual

Dim pt As PivotTable
Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets

If ws.Name = "PV Summary" Or ws.Name = "PV Past" Or ws.Name = "PV N" Then
'If Mid(ws.Name, 1, 2) = "PV" Then

For Each pt In ws.PivotTables


Next pt

End If

Next ws
    Sheets("PV Summary").Select
    Sheets("PV Summary").Columns("A:O").EntireColumn.AutoFit
    Sheets("PV Past").Select
    Sheets("PV Past Dues").Columns("A:O").EntireColumn.AutoFit
    Sheets("PV N").Select
    Sheets("PV N").Columns("A:O").EntireColumn.AutoFit
    Sheets("Workbook Contents").Select

MsgBox ("Pivots Done")
Application.ScreenUpdating = True
Application.Calculation = xlCalculationAutomatic
End Sub

Open in new window

Who is Participating?

Improve company productivity with a Business Account.Sign Up

BusyMamaConnect With a Mentor Commented:
Have you tried ActiveWorkbook.RefreshAll to save any time?  Not sure if it would or not.
montrofAuthor Commented:
No but does that refresh pivots.
Yes, that would refresh all of the pivot tables in the open, active workbook.

Also if the formatting is the same for every worksheet, you can probably select all of the worksheets and then do the formatting as one without cycling through each worksheet.  For example, if I manually select all of my worksheets (hold CTRL and click on each tab), I can apply formatting to all of them simply by formatting the one I am looking at.
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

montrofAuthor Commented:
Ok I tested it and it does refresh the pivots but it is not any faster.  Thanks for the suggestion.
Where is the data that is feeding the pivots, is it a large data set?  If so, maybe applying filters?
montrofAuthor Commented:
the data is in another tab and it is not that large it is only about 3000 rows and 18 columns.
Rory ArchibaldCommented:
Are your pivot tables all built off the same data set?
montrofAuthor Commented:
no but the other data sets are even smaller.
Rory ArchibaldCommented:
Do you have any code, charts, conditional formatting or volatile functions (like OFFSET) in the workbook?
Also, have you stepped through the code to see if the refresh is what takes the time, or any of the code afterwards?
montrofAuthor Commented:
I do not have any of the other stuff and it does seem to be the pivot table refresh.  I was wondering if I should try pt.manualupdate = true and see if that helps.
Rory ArchibaldCommented:
I'd be surprised if it would since you still have to update it at some point in your code.
montrofAuthor Commented:
This actually turned out to work great. The one that was going slow was because the excel file was corrupt.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.