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(objPivo t.Name) =
sSource

Next

End Sub

Sincerely,

Wayne Huxman