Pivot Tables

[Paul Grayson]

Hi,

I am using the following code to change the data of a pivot table. I am
wanting all my pivot to use the same data source as "PivotTable1".

Private Sub Worksheet_Activate()
Range("B17").Select
Activesheet.PivotTableWizard SourceType:=xlPivotTable,
SourceData:="PivotTable1"
End Sub

The above code is only referencing PivotTable1 on the same sheet. I need it
to reference PivotTable1 on a different sheet?

The problem is "PivotTable1" is on a different worksheet to all the other
pivots. How do I refereance in code that I want my Pivots in sheet2 to
reference the data source of the pivot in sheet1?

Hope the above makes sense.

Thanks

Paul

 

[Tom Ogilvy]

dim pCache as PivotCache
dim pt1 as PivotTable
set pCache = Worksheets("Sheet1").PivotTables("PivotTable1").PivotCache
set pt1 = pCache.CreatePivotTable( _
TableDestination:=Worksheets("sheet2").Range("B17"), _
TableName:="PivotTable1")

I forget which version added the pivottable object with the ability to
create pivottables, but hopefully something like the above will work for you.

 

[Paul Grayson]

I have tried this but because I have a pivot table called PivotTable1 on the
sheet I am trying to get the pivots updated I get the following error
message:-

'Run time error 1004'
A pivot table report with that name already exists on the destination sheet?

Any ideas how I can solve this?

Thanks again

Paul

 

[Tom Ogilvy]

Sure, give it a different name - one that you are not using.

 

[Alan]

Change the new PivotTable name to PivotTable2

dim pCache as PivotCache
dim pt1 as PivotTable
set pCache = Worksheets("Sheet1").PivotTables("PivotTable1").PivotCache
set pt1 = pCache.CreatePivotTable( _
TableDestination:=Worksheets("sheet2").Range("B17"), _
TableName:="PivotTable2")

I prefer to use pivot table names as a descriptor of what the pivot table
reports, such as TableName:="ByRegion" or "BySalesman" etc.... allowing
other coders, who might come behind me, to readily pick up on what code I
attached to what pivot table. Also, when loading a new pivot table with
code, since all the other pivot table names have to be considered (can not
match names), it is less likely to match an existing name if it is not in
the PivotTable1, PivotTable2,...etc range of names.

Alan


"The only dumb question is the question left unasked."