Solved

Pivot table refresh slow

Posted on 2011-09-06
12
1,486 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
  • 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
Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

 
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

Live: Real-Time Solutions, Start Here

Receive instant 1:1 support from technology experts, using our real-time conversation and whiteboard interface. Your first 5 minutes are always free.

Question has a verified solution.

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

Improved? Move/Copy Add-in Replacement - How to avoid the annoying, “A formula or sheet you want to move or copy contains the name XXX, which already exists on the destination worksheet.” David Miller (dlmille)  It was one of those days… I wa…
Workbook link problems after copying tabs to a new workbook? David Miller (dlmille) Intro Have you either copied sheets to a new workbook, and after having saved and opened that workbook, you find that there are links back to the original sou…
This Micro Tutorial demonstrates using Microsoft Excel pivot tables, how to reverse engineer competitors' marketing strategies through backlinks.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.

815 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now