dim pCache as PivotCache
dim pt1 as PivotTable
set pCache = Worksheets("Sheet1").PivotTables("PivotTable1").Pi votCache
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.

--
Regards,
Tom Ogilvy


"Paul Grayson" wrote:

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