Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Tables
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Tables
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Tables
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").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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Tables
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").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 |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Tables
Change the new PivotTable name to PivotTable2
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:="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" wrote in message ... 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").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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Building pivot tables in Excel 2007 based on existing pivot tables? | Excel Discussion (Misc queries) | |||
Pivot Table Data Adding contents of two pivot tables and param que | Excel Discussion (Misc queries) | |||
Average Calculations from Pivot Tables - Get Pivot Data? Calc Fiel | Excel Worksheet Functions | |||
How does the term 'pivot' apply to Excel's Pivot tables and Pivot. | Excel Discussion (Misc queries) | |||
Pivot tables-controlling user interaction with pivot tables | Excel Programming |