Creating Pivot Tables inside a Macro

[farrell77]

Does anyone know where examples can be found of creating pivot tables
in macros?

After some reading up on pivot tables, I went through the steps to create
one
that I needed and recorded the steps in a macro. But when I tried to run
the
macro, it failed on a line with the following in it:

....ActiveSheet.PivotTables("PivotTable13"). ...

Presumably, the problem is with the pivot table name. I'm looking for some
examples that show how to name a pivot table so I can have the same name
each time, as well as examples that show how to deal with other pitfalls.

Thanks.

- Bob

 

[Tom Ogilvy]

See Debra Dalgleish's site

See P and look at the entries for Pivot Tables.

http://www.contextures.com/tiptech.html

 

[farrell77]

Thanks for the excellent reference. I gather from scanning that site and
its links that the
way to programmatically create a Pivot Table is by calling
Worksheets("Pivot").PivotTableWizard. I'll experiment with this.

- Bob

 

[Tom Ogilvy]

A good way to start is to turn on the macro recorder (tools=>Macros=>Record
a macro), then create the pivot table manually, then turn off the macro
recorder and look at the code recorded. This gives you good insight most of
the time on what the properties and methods used are. Unfortunately it
records things like Select and scroll because that is what you are doing.
When you actually code things, selecting and certainly scrolling certainly
are not necessary.

 

[farrell77]

Tom,

Here's some code generated by the macro recorder:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Expanded Data'!R1C2:R898C45").CreatePivotTable TableDestination:= _
"'[OnStar Test.xls]ChartsState'!R2C1", TableName:="PivotTable13", _
DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables("PivotTable13").PivotFields("Month")
.Orientation = xlRowField
.Position = 1

This code was not re-runnable. After reviewing some of the
references you suggested, I modified it to something like this:

PivotTableName = "OnStarDataPivotTable"
Worksheets(RawDataTab).PivotTableWizard _
SourceType:=xlDatabase, _
SourceData:="'Expanded Data'!R1C2:R898C45", _
TableDestination:="'[OnStar Test.xls]PivotTables'!R2C1", _
TableName:=PivotTableName
With ActiveSheet.PivotTables(PivotTableName).PivotFields("Month")
.Orientation = xlRowField
.Position = 1
End With

This worked and was re-runnable. It uses the same table each time
and I have code above this to delete the old one each time before it's
created here.

I realize there's probably still a problem when my source data is
updated (which it will be) and the range goes beyond R898C45.
I need to address that eventually.

However as a matter of interest, there seems to be something
else about the recorded macro. It uses the PivotCaches.Add
method and the material I read said to use PivotTableWizard,
which I did above. But I was curious what would happen if
I simply followed the macro code more closely and did the
following:

PivotTableName = "OnStarDataPivotTable"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:= _
"'Expanded Data'!R1C2:R898C45").CreatePivotTable TableDestination:=
_
"'[OnStar Test.xls]PivotTables'!R24C1", TableName:=PivotTableName, _
DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables(PivotTableName).PivotFields("Month")
.Orientation = xlRowField
.Position = 1
End With

It didn't work and resulted in the following error message:

"Unable to get the PivotTables property of the Worksheet class"

I guess this surprised me (or I'm overlooking something obvious),
because it suggests that there are more complex issues when
you use the macro recorder to help generate code -- issues that
go beyond selecting, scrolling, generation of sequential names
for objects.

- Bob