Pivot Table - automatically determining range of Source Data

[Mopacs]

Hello again,

I just had a follow up to my previous post:

What I am trying to do is simply "refresh" an existing Pivot Table to reflect the source data range (ie, A2:G3002) at that moment. The source data range will fluctuate from week-to-week (ie, 3099 rows the next week). So I just want the pivot table to remain current, without having to manually go back into the "Pivot Table Wizard" and specify a new range each time (A2:G3099 this time). Does anyone have macro/VBA code which can dynamically update an existing pivot table such as this? Any assistance would be greatly appreciated.

Thanks,

Rob (again, I know!)

 

[Mark W.]

how do you dynamically change the named range?

I either update the named data list (e.g., Database) using Excel's built-in data form (i.e., the Data | Form... menu command) or define a name that refers to a formula that examines the key field(s) of the data list to determine the extent of the data list.
This message was edited by Mark W. on 2002-03-11 16:01

 

[John0898]

I couldn't get this to work either, even using the range definition, when it came to refreshing the pivot table, it wouldn't work