Solved

Pivot table refresh slow

Posted on 2011-09-06
12
1,430 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
 
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
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 
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

What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

Join & Write a Comment

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;…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
This Micro Tutorial will demonstrate how to use longer labels with horizontal bar charts instead of the vertical column chart.
This Micro Tutorial will demonstrate in Microsoft Excel how to add style and sexy appeal to horizontal bar charts.

757 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

22 Experts available now in Live!

Get 1:1 Help Now