Pivot Tables
in [Programming]
|
From: Paul Grayson on 30 Jan 2007 14:23 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
From: Tom Ogilvy on 30 Jan 2007 15:11 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. -- 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 > >
From: Paul Grayson on 30 Jan 2007 15:49 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" wrote: > 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. > > -- > 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 > > > >
From: Tom Ogilvy on 30 Jan 2007 16:15 Sure, give it a different name - one that you are not using. -- Regards, Tom Ogilvy "Paul Grayson" wrote: > 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" wrote: > > > 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. > > > > -- > > 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 > > > > > >
From: Alan on 30 Jan 2007 16:20 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." "Paul Grayson" <PaulGrayson(a)discussions.microsoft.com> wrote in message news:9FF3ECB6-AC5A-46AA-B613-565CCF77A778(a)microsoft.com... >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" wrote: > >> 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. >> >> -- >> 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 >> > >> >
|
Pages: 1 Prev: Dlook up Access Next: Compile error 'Method or data member not found' when closing Excel |