Update Pivots Dynamically

[Wayne Huxman]

I have spreadsheets that have multiple pivots tables and am looking for a
way to dynamically reset the source range. I've tried the code below (and
some variations) but always end up with 1 error or another. The other idea
to minimize the maintenance is to run as many pivots as possible off of a
master pivot (would probably help minimize File size etc also). Any
suggestions on how to approach this would be appreciated. Thanks in
advance.

Sub UpdatePivot()
Dim ObjPivot as Pivotable
Dim sSource as String

sSource = inputbox("Enter Pivot Source Range")


For each objPivot in ActiveworkBook.Sheets("Pivot").PivotTables
' I've tried the 2 line below separately, not bot in the same sub
set objPivot.Sourcedata = sSource
Set ActiveworkBook.Sheets("Pivot").PivotTables(objPivot.Name) =
sSource

Next

End Sub

Sincerely,

Wayne Huxman

 

[Debra Dalgleish]

You could change your code so you can select the source range, instead
of typing it, then use that range to get the source address:

'=====================
Sub UpdatePivotSource()
Dim ObjPivot As PivotTable
Dim rngSource As Range
Dim sSource As String
Set rngSource = Application.InputBox("Select the Pivot Source Range",
Type:=8)

sSource = "'" & rngSource.Parent.Name & "'!" &
rngSource.Address(ReferenceStyle:=xlR1C1)

For Each ObjPivot In ActiveWorkbook.Sheets("Pivot").PivotTables
ObjPivot.SourceData = sSource
ObjPivot.PivotCache.Refresh
Next
End Sub
'====================

To change the pivot cache, there's sample code here:

http://www.contextures.com/xlPivot11.html