getting a pivottable to automatically pick up new rows in the source?

[neowok]

i have a pivottable and chart based on it and it would be very useful i
i could make the table automatically change its source range to includ
any new rows added to the source table.

at the moment ive got the chart updating whenever the chart sheet i
activated, so ideally i would simply put code to update the pivottabl
with any extra rows just above this.

basicallly i want it to pick up all non blank rows from 4 down t
wherever the end of the table is, without me having to go into th
pivottable properties and changing the source every time i add a ne
row to the source table.

the other thing is i have colour the bars on the chart based on wha
data each bar is, but sometimes some of the bars will have no data an
so not appear in the chart, which screws up my code for assigning th
colours. what i need is code that will go from the first data serie
to whatever the last one happens to be, then inside this loop check th
name of the data series. i can then colour the bars by name. the cod
ive got colouring them at the moment is like:

ActiveChart.SeriesCollection(1).Select
ActiveChart.SeriesCollection(1).Points(1).Select
ActiveChart.SeriesCollection(1).Points(1).ApplyDataLabel
ShowValue:=True
With Selection.Interior
.ColorIndex = 39
End With
ActiveChart.SeriesCollection(1).Points(2).Select
ActiveChart.SeriesCollection(1).Points(2).ApplyDataLabel
ShowValue:=True
With Selection.Interior
.ColorIndex = 39
End With

theres up to 14 'points' but there might sometimes only be say 6, an
so the code would then break when trying to assign a colour to number
unless it only colours inside a loop that does something like wit
points(1) to points(last) if points.name=("my text") then .colorindex
mycolour"

i just dont know the exact code to do thi

 

[Frank Kabel]

Hi
have a look at
http://www.contextures.com/xlPivot01.html

 

[JE McGimpsey]

Also, in MacXL01/v.X and WinXL03, you can use the List Manager to define
a list for the Pivot Table and the List Manager will update the PT's
input range automatically (you still have to refresh, though).

 

[neowok]

only got 2002 here.

managed to sort the dynamic range easy enough.

just need to sort out colouring them bars no