Solved

Updating pivot tables in a workbook

Posted on 2009-05-12
4
612 Views
Last Modified: 2012-05-06
Whenever I run the macro below more than once I get this error message: "Excel cannot complete this task with available resources. Choose less data or close other applications." And then I get a VBA error message that says, "Run-time error '5': Invalid procedure call or argument."

I need to apply a macro that updates pivot table ranges across an entire workbook, my first attempt at this was to make one macro but I got the error message above. What I did then was to split the macro into three pieces. I run one macro and then it would update the pivot tables in sheets: "Center", "Sony", "Howard", and "Conference". Then i would run another macro that would do it in the sheets after that, etc. I can update the first set of sheets but when I try and run the second maco half way through I get the error message that I got above.


Public Sub UpdAllPivots()

  Dim shtThing As Worksheet

  Dim pvtThing As PivotTable

  For Each shtThing In Application.Sheets

  If shtThing.Name = "Center" Or shtThing.Name = "Sony" Or shtThing.Name = "Howard" Or shtThing.Name = "Conference" Then

    For Each pvtThing In shtThing.PivotTables

      pvtThing.ChangePivotCache ActiveWorkbook. _

        PivotCaches.Create(SourceType:=xlDatabase, _

        SourceData:="C:\Documents and Settings\shahryarm\Desktop\[Combined 09S 20090407v8.xlsm]Combined Grantee Main Data!R3C4:R50000C252", _

        Version:=xlPivotTableVersion12)

    Next

    End If

  Next

End Sub

Open in new window

0
Comment
Question by:hermes829
  • 2
  • 2
4 Comments
 
LVL 16

Expert Comment

by:Jerry Paladino
ID: 24400487
Hermes829,
Have you tried this with a smaller dataset?  Your 50,000 rows and 252 columns is 12,600,000 cells of data.  Each Pivot Table by default holds a copy of the data so you could truly be running out of resources.  If you have 10+ pivot tables it may be too much without setting PT options to disable "Save source data with file".  If the dataset does not truly have 50,000 rows and you used that number to make sure you catch the bottom of the data then perhaps you can use a dynamic range name to define the data area.  
Some sample code is here - http://www.experts-exchange.com/Q_24173598.html but it is very similar to what you have so I don't expect it to resolve the problems you are having.  I would start with reducing the data to 500 rows and 15 columns just to work out any issues with the VBA and try to pinpoint the root cause of the problem.
HTH,
Jerry
0
 

Author Comment

by:hermes829
ID: 24424358
Thanks I'm afraid that the dataset is the size it is. We just have too many pivot tables referencing a dataset that's too big to continue using this type of format. Do you have any suggestions for database programs to switch to?
0
 
LVL 16

Accepted Solution

by:
Jerry Paladino earned 500 total points
ID: 24424585
Yes, I use MS-Access and its query engine to summarize large dataset.   With Excel Pivot Table you can use "External Data" and point them to an Access query as the datasource.   Do the majority of your summarization in Access and then report with your Excel Pivot Tables.    You can drastically reduce the size of the dataset that are stored with the pivot tables this way.
Another option is to use Excel's built in tool MS-Query to pull the Access query results into an excel sheet and then base your pivot table on that summarized sheet.  Several options.
HTH,
Jerry
0
 

Author Closing Comment

by:hermes829
ID: 31580604
Awesome thank you very much for this information.
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Excel 2013 Problem 12 48
Excel - Increse columns in range by 5 6 22
How to Add / Edit Windows Menu 4 29
3rd level dependant list 4 33
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.
The viewer will learn how to simulate a series of coin tosses with the rand() function and learn how to make these “tosses” depend on a predetermined probability. Flipping Coins in Excel: Enter =RAND() into cell A2: Recalculate the random variable…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

744 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

12 Experts available now in Live!

Get 1:1 Help Now