PDA

View Full Version : Pivottable macro - ManualUpdate problem



JWhite
04-17-2008, 04:18 PM
I'm writing Pivottable macros for the first time and they generally work well. My only problem is that I can't get the ManualUpdate (which controls the Defer Layout Update) property to work. It's important because you get a macro error if you do a "ClearTable" while the Defer Layout Update is checked.

My code starts like this:

Sub PivotRep1()
Dim pt As PivotTable
Worksheets("Sheet1").Select
Set pt = ActiveSheet.PivotTables("PivotTable1")
pt.ClearTable
pt.Location = "Sheet1!B1"
Range("B1").Select
ActiveWorkbook.ShowPivotTableFieldList = True
ActiveSheet.PivotTables("PivotTable1").ManualUpdate = True

In this case, I'm clearing an existing report before running the new one and then moving the Pivottable back to its original location (because if you use filters it moves the Pivottable down from its original location). It works just fine except the last statement doesn't do anything. In the Immediate window, I type "?pt.manualupdate" and I get False every time. I can check and uncheck the "Defer Layout Update" box and I still get a False response every time. I even tried recording a macro where I checked the box and the recorder doesn't show ANYTHING except the comment lines in the macro.

I see this code in other people's macros I assume it CAN work. All my other code is working. Why isn't this? Thanks in advance!

John

JWhite
04-18-2008, 09:17 AM
Forgot to add that I'm running a brand-new install of Excel 2007 v12.0 SP1.

Also, I asked a friend with Excel 2007 to record a macro and click on the Defer Layout Update button and it didn't record anything on his computer either. I've been Google'ing the problem and EVERYONE else seems to be using this with no problem.