?
Solved

Pivot table refresh slow

Posted on 2011-09-06
12
Medium Priority
?
1,781 Views
Last Modified: 2012-05-12
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
Comment
Question by:montrof
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
  • 3
12 Comments
 
LVL 7

Accepted Solution

by:
BusyMama earned 2000 total points
ID: 36490542
Have you tried ActiveWorkbook.RefreshAll to save any time?  Not sure if it would or not.
0
 
LVL 1

Author Comment

by:montrof
ID: 36490593
No but does that refresh pivots.
0
 
LVL 7

Expert Comment

by:BusyMama
ID: 36490610
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
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.

 
LVL 1

Author Comment

by:montrof
ID: 36490612
Ok I tested it and it does refresh the pivots but it is not any faster.  Thanks for the suggestion.
0
 
LVL 7

Expert Comment

by:BusyMama
ID: 36490622
Where is the data that is feeding the pivots, is it a large data set?  If so, maybe applying filters?
0
 
LVL 1

Author Comment

by:montrof
ID: 36490641
the data is in another tab and it is not that large it is only about 3000 rows and 18 columns.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36490849
Are your pivot tables all built off the same data set?
0
 
LVL 1

Author Comment

by:montrof
ID: 36490851
no but the other data sets are even smaller.
0
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36490960
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
 
LVL 1

Author Comment

by:montrof
ID: 36490975
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
 
LVL 85

Expert Comment

by:Rory Archibald
ID: 36491278
I'd be surprised if it would since you still have to update it at some point in your code.
0
 
LVL 1

Author Closing Comment

by:montrof
ID: 36512214
This actually turned out to work great. The one that was going slow was because the excel file was corrupt.
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need to forecast numbers -- typically for finance -- the Windows and Mac versions of Excel 2016 have a basket of tools to get the job done.
The viewer will learn how to create a normally distributed random variable in Excel, use a normal distribution to simulate the return on an investment over a period of years, Create a Monte Carlo simulation using a normal random variable, and calcul…
Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…

719 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question