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

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

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