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

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
>
>
>
>

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" <[email protected]> wrote in message
news:[email protected]...
> 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
>>
>>
>>
>>