Update Pivot Tables using VBA 2

[nic6000]

Hi,

Im trying to find a way of updating an exisiting Pivot Table when the data range changes e.g one week a data table on a worksheet might have 300 rows, the next it might have 350 rows. The column headers are static.

The refresh data property only updates data from the previously defined rows

I've tried several properties such as DataBodyRange, Row Range but can't seem to get any of them to work! Either I'm way off here, or my syntax is completly wrong.

Please can any one help on this one, I normally try to avoid Pivot Tables, but have no choice this time around!

Thanks in advance,

Nic

 

[PHV]

Have a look here:
faq68-1331

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SkipVought]

Hi,

"I normally try to avoid Pivot Tables."

Why? PTs are a vital tool. You ought to get up-to-speed on PTs.

I posted a very detailed description related to this very issue in thread68-1365696. No VBA Code required, althought it can also be done with code.



Skip,

_[red][/red]

 

[xlbo]

second the usage of pivottables - VERY useful tool...

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[nic6000]

Thanks PVA and Skip,

Named Ranges work OK

Skip / xlbo

I don't mind Pivot tables for quick lookups, but I'm usually required to produce presentable static tables of data thus negating the need for Pivots. All calcs are normally done in Access. I also think there is better software on the market for producing 'drill downs' - but I guess we can only use the tools we have to hand.

Thanks again,

Nic

 

[SkipVought]

Then you might consider using Data/Get External Data...

Skip,

_[red][/red]

 

[xlbo]

presentable static tables of data

yes....and...pivot tables can be formatted as well as anything else with the added benefit that if you set them up properly, the effort to update is virtually nil.

They are also a very good quick summarising and sense checking tool

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question