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. _
SourceData:="C:\Documents and Settings\shahryarm\Desktop\[Combined 09S 20090407v8.xlsm]Combined Grantee Main Data!R3C4:R50000C252", _