Solved

Pivot table refresh slow

Posted on 2011-09-06
12
1,592 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 500 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
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

Instantly Create Instructional Tutorials

Contextual Guidance at the moment of need helps your employees adopt to new software or processes instantly. Boost knowledge retention and employee engagement step-by-step with one easy solution.

Question has a verified solution.

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

How to get Spreadsheet Compare 2016 working with the 64 bit version of Office 2016
This article describes a serious pitfall that can happen when deleting shapes using VBA.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

752 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