Creating Pivot Tables inside a Macro
 

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

Creating Pivot Tables inside a Macro
 

See Debra Dalgleish's site

See P and look at the entries for Pivot Tables.

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

--
Regards,
Tom Ogilvy

"farrell77" wrote in message
...
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

Creating Pivot Tables inside a Macro
 

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" wrote in message
...
See Debra Dalgleish's site

See P and look at the entries for Pivot Tables.

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

--
Regards,
Tom Ogilvy

"farrell77" wrote in message
...
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

Creating Pivot Tables inside a Macro
 

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.

--
Regards,
Tom Ogilvy

"farrell77" wrote in message
...
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" wrote in message
...
See Debra Dalgleish's site

See P and look at the entries for Pivot Tables.

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

--
Regards,
Tom Ogilvy

"farrell77" wrote in message
...
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

Creating Pivot Tables inside a Macro
 

Tom,

Here's some code generated by the macro recorder:

ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _
"'Expanded Data'!R1C2:R898C45").CreatePivotTable TableDestination:= _
"'[OnStar Test.xls]ChartsState'!R2C1", TableName:="PivotTable13", _
DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables("PivotTable13").PivotField s("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).PivotField s("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:=xlDatab ase, SourceData:= _
"'Expanded Data'!R1C2:R898C45").CreatePivotTable TableDestination:=
_
"'[OnStar Test.xls]PivotTables'!R24C1", TableName:=PivotTableName, _
DefaultVersion:=xlPivotTableVersion10
With ActiveSheet.PivotTables(PivotTableName).PivotField s("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

"Tom Ogilvy" wrote in message
...
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.

--
Regards,
Tom Ogilvy

"farrell77" wrote in message
...
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" wrote in message
...
See Debra Dalgleish's site

See P and look at the entries for Pivot Tables.

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

--
Regards,
Tom Ogilvy

"farrell77" wrote in message
...
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