Change source of pivot table dynamically based on sheet name

Hi,

Not quite sure what you're getting at.

The source for pivot tables should always be a dynamic range name so that as new data is added the PT can be refreshed to accomodate it, and I usually include an Activeworkbook.Refreshall line of code in some approriate event procedure, like say a SheetActivate procedure

But I'm puzzled why you need many data sheets and many pivot tables. The smart thing to do is always hold all your data in a single database and worry about reporting it afterwards - which can usually be done fairly easily with a Pivot Table and appropriate Slicers, or some Advanced Data Filter.