excel - Automatically update PivotTable data source -

excel - Automatically update PivotTable data source -

using ole' search stackoverflow provides, found next code

sub change_pivot_source() dim pt pivottable each pt in activeworkbook.worksheets("funnel info pivots").pivottables pt.changepivotcache activeworkbook.pivotcaches.create _ (sourcetype:=xldatabase, sourcedata:="lead info (q1-q2)") next pt end sub

where "funnel info pivots" woorksheet contains pivot charts trying update, , "lead info (q1-q2)" info source of pivots....each week info source extended (we input lines of info @ bottom, keeping rest of info is)....yet code fails render, saying "reference not valid" (on lead info (q1-q2) line...i assumed typod, changed name in worksheet (and in vba) much simpler text...yet still failed.

so

a) right code want utilize extend info source of pivot tables?

and

b) if is, how debug it, if it's not right code, is?

this original vba function, updated pivot tables, not info source

sub allworksheetpivots1() dim pt pivottable each pt in activesheet.pivottables pt.refreshtable next pt end sub

thanks time!

the sourcedata argument requires range object per help, appears supplying sheet name. experience wants string represents range. should using like

sourcedata:="lead info (q1-q2)!a1:d100"

but improve if made info table , based pivottable on that. pivot table automatically expand add together new rows.

excel vba excel-2010