Running Macro always has error at pivot table creation

**culverhr** · 11-29-2012, 11:55 AM

Good afternoon to all the smart excel people out there. I need some help. I created a macro which creates a couple of pivot tables and then moves the data which feeds into an "overview" worksheet. However, everytime I run it I get an error at one of the pivot tables. The error is as follows:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
"nov28!R1C1:R65536C25", Version:=xlPivotTableVersion10).CreatePivotTable _
TableDestination:="Sheet6!R3C1", TableName:="PivotTable1", DefaultVersion _
:=xlPivotTableVersion10

Any help on how to solve this? I don't have a clue.

**Norie** · 11-29-2012, 12:11 PM

What's the error message?

**GeneralDisarray** · 11-29-2012, 12:12 PM

You might want to try this approach instead. Just create the pivot tables as part of the template and assign them dynamic-named range. The dynamic-named-range will expand/contract to reflect the data in the overview worksheet.

Then, when the macro runs you can just ask the pivot tables to update. Actually, if the pivot tables are in a separate worksheet (which is usually a good idea also) you can handle the pivot table refreshing with an event macro. Whenever the worksheet with the pivot table is selected, all tables on the sheet can be asked to refresh themselves.

Can you post an example?

Running Macro always has error at pivot table creation

LinkBack

Thread Tools

Rate This Thread

Display

Running Macro always has error at pivot table creation

Re: Running Macro always has error at pivot table creation

Thread Information

Users Browsing this Thread

Bookmarks

Bookmarks

Posting Permissions