Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1860
  • Last Modified:

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

pt.RefreshTable


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

0
montrof
Asked:
montrof
  • 6
  • 3
  • 3
1 Solution
 
BusyMamaCommented:
Have you tried ActiveWorkbook.RefreshAll to save any time?  Not sure if it would or not.
0
 
montrofAuthor Commented:
No but does that refresh pivots.
0
 
BusyMamaCommented:
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.
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
montrofAuthor Commented:
Ok I tested it and it does refresh the pivots but it is not any faster.  Thanks for the suggestion.
0
 
BusyMamaCommented:
Where is the data that is feeding the pivots, is it a large data set?  If so, maybe applying filters?
0
 
montrofAuthor Commented:
the data is in another tab and it is not that large it is only about 3000 rows and 18 columns.
0
 
Rory ArchibaldCommented:
Are your pivot tables all built off the same data set?
0
 
montrofAuthor Commented:
no but the other data sets are even smaller.
0
 
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?
0
 
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.
0
 
Rory ArchibaldCommented:
I'd be surprised if it would since you still have to update it at some point in your code.
0
 
montrofAuthor Commented:
This actually turned out to work great. The one that was going slow was because the excel file was corrupt.
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 6
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now