Solved

Pivot table refresh slow

Posted on 2011-09-06
12
1,535 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
Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
question about excel functionality 3 33
Date Formatting on Userform Print 5 27
Excel formula - data format 5 22
Index Match Array? 5 40
This tutorial explains how to create a series of drop-down lists that are dependent upon prior selections to guide (“force”) the user to make the correct selection and reduce data errors within Microsoft Excel. Excel 2010 was used for this tutorial;…
Freeze panes is an option within all variants of Excel to enable parts of a sheet to remain stationary when the cursor is in another part of the sheet. This is a very useful feature which is overlooked or under used.
This Micro Tutorial will demonstrate the scrolling table in Microsoft Excel using the INDEX function.
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

830 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