Change Pivot Table Range
 

G'Day All,

I have a workbook that has a Worksheet containing raw data read from an
Access database using VBA/ADODB using criteria selected from a UserForm.

Several other Sheets contain summary reports of various kinds assembled
from the Raw Data using VBA.

I would like to construct two Pivot Tables/Charts based on the Raw Data.

How may I update the underlying range of these, when the raw data range
changes from time to time depending on the criteria?
--
Regards,
Pat Garard
Melbourne, Australia
_______________________

Change Pivot Table Range
 

Pat,

This is a scheme that works for me.
1) use a named range (PIVDATA) as the source of the pivot table
2) use VBA to set the range you would like to that name (PIVDATA)
3) use VBA to refresh all the pivot tables in the workbook

For step 1 : OPEN PIVOT TABLE WIZARD and enter the named range at step 2 of 3

For step 2 something like this. Assume B1 is a cell in the range you want
to use in the pivot table then:


"Pat Garard" wrote:

G'Day All,

I have a workbook that has a Worksheet containing raw data read from an
Access database using VBA/ADODB using criteria selected from a UserForm.

Several other Sheets contain summary reports of various kinds assembled
from the Raw Data using VBA.

I would like to construct two Pivot Tables/Charts based on the Raw Data.

How may I update the underlying range of these, when the raw data range
changes from time to time depending on the criteria?
--
Regards,
Pat Garard
Melbourne, Australia
_______________________

Change Pivot Table Range
 

Pat,

Here is a scheme that works for me.
1) Name the range the pivot table refers to (PIVDATA)
2) use VBA to change what that name refers to - the range you'd like to use
in the current instance
3) use VBA to refresh the pivot tables after the name change

For Step 1 : open the pivot table Wizzard and set the range in step 2 of 3
to PIVDATA
For Step 2: assume B1 is a cell in the range you'd like to use
range("B1").select
activecell.currentregion.name = "PIVDATA"
For Step3:
for each PT in activesheet.pivottables
.refreshtable
next

Step 2 and 3 can be tied to buttons or called from the macro that determines
which range you need to view.

This assumes the categories you are using in your pivot tables will stay
pretty much unchanged from range to range. If they don't then the selections
available will get messy fast.

Regards,
Mike

"Pat Garard" wrote:

G'Day All,

I have a workbook that has a Worksheet containing raw data read from an
Access database using VBA/ADODB using criteria selected from a UserForm.

Several other Sheets contain summary reports of various kinds assembled
from the Raw Data using VBA.

I would like to construct two Pivot Tables/Charts based on the Raw Data.

How may I update the underlying range of these, when the raw data range
changes from time to time depending on the criteria?
--
Regards,
Pat Garard
Melbourne, Australia
_______________________