PivotTable Source Name

[MacroAlan]

I am creating a Pivot Table on the fly that will be used in a further step. The problem is that the SourceData table changes name each time.

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
              SourceData:=[b]"Table5"[/b], _
         Version:=xlPivotTableVersion14).CreatePivotTable TableDestination _
        :=TheName & "!R1C1", TableName:="Pivot" & ShtNm, DefaultVersion:= _
        xlPivotTableVersion14

Can I specify a generic name or a compiled one? Code sample?


Alan

 

[SkipVought]

Hi,

1) can you explain why you need to create a PT on the fly? In some 20 years of automating Excel, I don't believe I ever had to do this.

2) Immediately after adding a PT, use the PT count as the index to reference the newly added PT and change the name.

 

[MacroAlan]

I am drilling down from a chart and it gives me the drill-down data. I'm putting that into a new pivot table from which to construct more charts.

The Excel is just a stopgap until the .NET guys can built a web interface to the data.


Alan

 

[SkipVought]

How can a chart give you data for s PT?

I've drilled down from the chart source data, however not creating a PT, butmy custom summary data,

 

[SkipVought]

unless you have a complex data structure, I'd venture to say that there is a predictable drill down path, that is based on the selection of one or more parameters at each level. So I use a series of Queries to effect the drill down, where the query resultset becomes the source for another chart (or even the same chart!) no need for adding PTs on the fly.

 

[Gavona]

I am assuming that when you drill down the drill-down results are put into a new sheet starting at A1 and that sheet is activated. (That's what happens with pivot tables not so sure about charts.

Instead of
SourceData:="Table5"
how about:
SourceData=Activesheet.cells(1,1).currentregion
or
SourceData=Activesheet.UsedRange

Gavin