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

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" <farrell77@spamfree.yahoo.com> wrote in message
news:Lt9Pd.16$ZJ.10398@news.uswest.net...
> 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
>
>

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" <twogilvy@msn.com> wrote in message
news:110qc80bgh855d6@news.supernews.com...
> 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" <farrell77@spamfree.yahoo.com> wrote in message
> news:Lt9Pd.16$ZJ.10398@news.uswest.net...
> > 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
> >
> >
>
>

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" <farrell77@spamfree.yahoo.com> wrote in message
news:xooQd.5$1t3.551@news.uswest.net...
> 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" <twogilvy@msn.com> wrote in message
> news:110qc80bgh855d6@news.supernews.com...
> > 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" <farrell77@spamfree.yahoo.com> wrote in message
> > news:Lt9Pd.16$ZJ.10398@news.uswest.net...
> > > 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,

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

"Tom Ogilvy" <twogilvy@msn.com> wrote in message
news:OmRHcZ9EFHA.2876@TK2MSFTNGP12.phx.gbl...
> 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" <farrell77@spamfree.yahoo.com> wrote in message
> news:xooQd.5$1t3.551@news.uswest.net...
> > 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" <twogilvy@msn.com> wrote in message
> > news:110qc80bgh855d6@news.supernews.com...
> > > 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" <farrell77@spamfree.yahoo.com> wrote in message
> > > news:Lt9Pd.16$ZJ.10398@news.uswest.net...
> > > > 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
> > > >
> > > >
> > >
> > >
> >
> >
>
>