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

You don't update the PivotTable's data range. You use a named range (e.g., Database) that is updated each time the data list is extended.

[karraj]

Assign a cell say a1 to hold the value of number of rows in your original data in the pivot table using the counta(g:g). then use concatenate to create the string ("a1: g" , a1) in a cell say a2
Then in the pivot table wizard use indirect(a2) and refresh, it works like a charm.

[Mark W.]

...Then in the pivot table wizard use indirect(a2) and refresh, it works like a charm.

Don't quite understand why you think this works -- it doesn't. The PivotTable wizard expects a range or named range and Step 2 of 4. Nothing else will work!

[karraj]

cell a2 has a range address!

[Mark W.]

On 2002-03-11 15:06, karraj wrote:
cell a2 has a range address!

A2 has a text representation of a cell range. That's quite different! Furthermore, Step 2 of 4 in the PivotTable wizard will only accept a range or named range. =INDIRECT(A2) is neither of these. It's a formula.

[karraj]

I looked up my model. I have named A2 as sourcerange and using indirect(sourcerange) in step2 of pivot table wizard. It works.

[Mark W.]

On 2002-03-11 15:25, karraj wrote:
I looked up my model. I have named A2 as sourcerange and using indirect(sourcerange) in step2 of pivot table wizard. It works.

What version of Excel are you using? XL97 will not accept INDIRECT(A2) at Step 2 of 4. XL2000 "chokes" at Step 3 of 3 when you attempt to define the Layout.

Both XL97 and XL2000 will allow you to create a named range that refers to =INDIRECT(A2) and then use that named range at Step 2, but this is approach has no advantages over my original suggestions. Nothing is gained by storing a text representation in a worksheet cell and then referencing it from a named range.

[ This Message was edited by: Mark W. on 2002-03-11 15:42 ]

[karraj]

I first created the pivot table and then did this approach to make my table dynamic.

how do you dynamically change the named range?

[karraj]

I am using XL97 and did not encounter any problems.