Pivot Table - automatically determining range of Source Data

Mopacs

New Member
Joined
Mar 6, 2002
Messages
33
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!)
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
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.
 
Upvote 0
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.
 
Upvote 0
...Then in the pivot table wizard use indirect(a2) and refresh, it works like a charm.
:confused: 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!
 
Upvote 0
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.
 
Upvote 0
I looked up my model. I have named A2 as sourcerange and using indirect(sourcerange) in step2 of pivot table wizard. It works.
 
Upvote 0
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
 
Upvote 0
I first created the pivot table and then did this approach to make my table dynamic.

how do you dynamically change the named range?
 
Upvote 0

Forum statistics

Threads
1,213,497
Messages
6,113,999
Members
448,543
Latest member
MartinLarkin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top