auto updating a pivotchart?

[neowok]

i have a data table, and on another sheet i have a pivottable based o
this, and then on a 3rd sheet i have a pivotchart based on th
pivottable.

what would be useful would be having the pivotchart and pivottabl
update automatically when things are added or changed on my main dat
table.

i have the chart updating on load using the checkbox in char
properties, but there doesnt seem to be one to update it when the mai
data table is updated

 

[Tom Ogilvy]

Use the change event for the sheet where the data table is. If a change is
made in the area of the table, execute a command to refresh the pivot table.

 

[neowok]

ive found ActiveChart.PivotLayout.PivotTable.RefreshTable

which is what recording a macro generates. but i think thats going t
fail if i have more than one pivotchart because it doesnt seem to b
referencing it by name.

clicking the chart doesnt seem to show the chart name so how would
find the name and reference it by name so i could update more than on
chart rather than just the 'activechart'

 

[Tom Ogilvy]

Worksheets("Sheet3").PivotTables(1).RefreshTable

I believe if you refresh the pivottable, the pivot chart will update
automatically.

Change Sheet3 to reflect the sheet name for the sheet where the pivot table
is located.

 

[neowok]

what effect is this going to have if i have 2 pivottables on sheet3?
there doesnt appear to be a way of referencing a pivottable by nam
(because there doesnt actually seem to BE a name for the table). and
probably will have more than one pivottable on a sheet.

thank

 

[Tom Ogilvy]

Sure a pivot table has a name. the default is PivotTable1 or PivotTable2,
etc.

? activesheet.PivotTables(1).Name
PivotTable1


Worksheets("Sheet3").PivotTables("PivotTable1).RefreshTable

If they share the same pivotcache, then they all get updated I believe.

 

[neowok]

strange, where does it show the name of the table? highlighting th
whole table just shows a cell number still.

must show the tables name somewhere

 

[Tom Ogilvy]

Click in the pivot table.

Right click and choose table options from the popup menu.

In the resulting dialog, the name is in the textbox at the top.

 

[neowok]

ahh nice of them to hide it in there going against the way everythin
else in excel shows its name when you select it (which is in the nam
box just below the open button surprisingly enough).

strange that the code Worksheets("Planned+Actual men char
data").PivotTables(1).RefreshTable did actually work, even though m
pivottable is called "pivottable2" hmm

yet using Worksheets("Planned+Actual men char
data").PivotTable2.RefreshTable gives an error saying the objec
doesnt support this property or metho

 

[Tom Ogilvy]

PivotTable2 is not a class name like when you have a command button. It is a
label usable in collections (the pivottables collection). there is not
particular correlation between the first member of the class PivotTables(1)
and the name of that member "Pivottable2"

 

[neowok]

so if i was to have 2 pivottables how would i refer to one or the other
pivottables(1) and pivottables(2) ? if thats the case how would
know which one is referring to which table heh

 

[Tom Ogilvy]

with Activesheet
msgbox .Pivottables(1).Name & vbNewLine
" located at " & pivottables(1).TableRange2(1).Address _
& vbNewline & vbNewline & _
.Pivottables(2).Name & vbNewLine
" located at " & pivottables(1).TableRange2(2).Address
End with

I guess it really depends on what you are trying to do.