[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1937
  • 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
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

 
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

The 14th Annual Expert Award Winners

The results are in! Meet the top members of our 2017 Expert Awards. Congratulations to all who qualified!

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