Updating Pivot Table Range in VBA

[dwipper]

I am trying to figure out how, with VBA, to update the range covered by a pivot table. I tried to record a macro, but nothing of use came out. Any suggestions would be appreciated.

 

[DaleWatson123321]

dwipper,

Here's a routine I would normally create...

Sub Set_Data()
Worksheets("Database&quot.Select 'see 1)
Application.Goto Reference:="dbtop" 'see 2)
topcell = ActiveCell.Address
botmcell = [A65536].End(xlUp).Offset(0, 10).Address 'see 3)
rng = topcell & ":" & botmcell
Range(rng).Name = "data" 'see 4)
End Sub

These are variables within this routine that you'll need to change to fit your situation:

1) "Database" - change this to the name of your sheet containing your pivot table.

2) "dbtop" - This is a range name assigned to the top-left-cell of your pivot table range.

3) The column offset (10) - change this to the number of columns for the cursor to move from the first column to the last column of your pivot table.

4) "data" - This is a range name used to define the entire range of data of your pivot table. Change this range name to match whatever range name you are using elsewhere in your VBA code to reference your pivot table range.

I hope this helps. Please advise as to how it fits.

Regards, ...Dale Watson dwatson@bsi.gov.mb.ca

 

[dwipper]

Dale,

Thanks for the tip. Here's how I wound up solving this:

'Select the sheet I want to work with
Worksheets("Quarterly Totals&quot.Select

'Select first cell below headers
Range("A2&quot.Select

'Note: only needed last row since last column is static
' in this situation
Selection.End(xlDown).Select

strLastCell = "'Quarterly Totals'!R1C1:R" & Trim(Str(Selection.Row)) & "C13"

'Update the range for the Pivot Cache
Worksheets("Total T&E Exp Pivot&quot.PivotTables(1).PivotCache.SourceData = strLastCell

'Get rid of any old values in the Pivot Cache
Worksheets("Total T&E Exp Pivot&quot.PivotTables(1).PivotCache.MissingItemsLimit = 0

'Refresh the Pivot Table with the new data from
' the PivotCache
Worksheets("Total T&E Exp Pivot&quot.PivotTables(1).PivotCache.Refresh

I like your approach of going bottom up to find the last row, although in my application, I know that there will be no blanks in my first column, so starting from the first cell and going down works.

Thanks again for your help.

The Wip