Reset a pivot table range using VBA 1

[loujcarr]

I have a spreadsheet with a table of data and I am running a pivot table from this data.

The table of data will have extra information added to it so I want to be able to re-set the range that the pivot table uses automatically but I can't seem to code it.

I have used a For...Next statement to loop through the data table until it finds a blank row but I can't find out how to re-set the pivot table range.

Any help greatly appreciated!

 

[Nikita6003]

Hiya,

though I'm no pivottable expert in VBA, I think you can use the PivotTableWizard to reset the data range of your pivottable

Try this:

    Dim l_wksPivotSheet As Worksheet
    
    'Replace PivotSheet with the correct name of your Pivot table sheet
    Set l_wksPivotSheet = ThisWorkbook.Sheets("PivotSheet")
    'Replace "PivotSourceSheet with name of pivot source data sheet
    'Replace PivotTable1 with name of your pivottable
    l_wksPivotSheet.PivotTableWizard xlDatabase, "PivotSourceSheet!R1C1:R39C3", , "PivotTable1"
    Set l_wksPivotSheet = Nothing

HTH

Cheers
Nikki

 

[xlbo]

OR - for infinitley more fun, try this
If your base data is on a sheet called "Data", go to Insert>Name>Define and enter a new name. Then enter this formula into the box where you would normally enter a range ref:
=OFFSET(Data!$A$1,0,0,COUNTA(Data!$A:$A),COUNTA(Data!$1:$1))

This will create a DYNAMIC range name that will expand and contract as you enter / delete data. Manually assign this range name to the pivot table source data by right clicking on the PT and going BACK once - enter the range name instead of the range ref. Once this is done, you'll never have to update the range again - simply refresh the pivot table

And Nikki - absolutley correct (for xl97 and 2000) but not sure about 2002 / XP etc Rgds
Geoff

Vah! Denuone Latine loquebar? Me ineptum. Interdum modo elabitur

 

[Nikita6003]

whew - that's a relief!

Will try for XP at home - have 97 here, but both 2k & xp set up on my poisonal pcs
Have to start using pivots for a project here so "ai'll bie bak ..."

Cheers
Nikki

ps - cool about the dynamic range - worth a star in my book