Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table problems
Hi,
1) Can I have 2 different PTables in a workbook that use the same sheet as data? It seems like if I copy a PTable, and then make changes in the copy, it changes the original too! How to fix this? 2) I'd like to keep the PTable references updated for a chart that pulls data from it. Is there code that can be written to a button that would update the PTable from additional data added to a certain sheet, and then refresh the chart that draws from the PTable? Thanks! Randy Starkey |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table problems
Question 1...
When you orignally created the pivot table it probably asked you if you would like to base the second table on the first to save memory. As a guess you said yes. You should have said no. By using the same memory, changes to one pivot are made to both pivots. You need to recreate the second pivot and when asked to base the data on the first... just say no... Now the two tables won't be linked and you are off to the races... Question 2... Base your pivot table data on a named range. Now when you change the named range you change the underlying data for the pivot table. Here is some code that I use.. ThisWorkbook.Names.Add "RawData", Range(shtRawData.Range("A65535").End(xlUp), shtRawData.Range("Z1")) shtReport.PivotTables(1).RefreshTable Not that I have named my sheets in code shtReport and shtRawData. You probably want to use sheets("Sheet1")... HTH "Randy Starkey" wrote: Hi, 1) Can I have 2 different PTables in a workbook that use the same sheet as data? It seems like if I copy a PTable, and then make changes in the copy, it changes the original too! How to fix this? 2) I'd like to keep the PTable references updated for a chart that pulls data from it. Is there code that can be written to a button that would update the PTable from additional data added to a certain sheet, and then refresh the chart that draws from the PTable? Thanks! Randy Starkey |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Pivot Table problems
Jim,
Thanks for the tips. I actually copied my pivot table sheet to duplicate it and then tried to change the data grouping. I guess copying shares the same memory? Any way around that or do I need to build the second pivot table from a clean sheet? Thanks! --Randy Starkey "Jim Thomlinson" wrote in message ... Question 1... When you orignally created the pivot table it probably asked you if you would like to base the second table on the first to save memory. As a guess you said yes. You should have said no. By using the same memory, changes to one pivot are made to both pivots. You need to recreate the second pivot and when asked to base the data on the first... just say no... Now the two tables won't be linked and you are off to the races... Question 2... Base your pivot table data on a named range. Now when you change the named range you change the underlying data for the pivot table. Here is some code that I use.. ThisWorkbook.Names.Add "RawData", Range(shtRawData.Range("A65535").End(xlUp), shtRawData.Range("Z1")) shtReport.PivotTables(1).RefreshTable Not that I have named my sheets in code shtReport and shtRawData. You probably want to use sheets("Sheet1")... HTH "Randy Starkey" wrote: Hi, 1) Can I have 2 different PTables in a workbook that use the same sheet as data? It seems like if I copy a PTable, and then make changes in the copy, it changes the original too! How to fix this? 2) I'd like to keep the PTable references updated for a chart that pulls data from it. Is there code that can be written to a button that would update the PTable from additional data added to a certain sheet, and then refresh the chart that draws from the PTable? Thanks! Randy Starkey |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivot Table Problems | Excel Worksheet Functions | |||
Pivot Table Problems | Excel Worksheet Functions | |||
pivot table problems | Excel Worksheet Functions | |||
pivot table problems | Excel Worksheet Functions | |||
Pivot table problems | Excel Discussion (Misc queries) |