Macro to change Pivot Data to new sheet, where sheet name will change

[sarails]

Hi Folks,

I have a spreadsheet that includes a number of Pivots that require changeing the Pivot Table data on a daily basis to another sheet, this gives a daily variance to two pivot tables.

I have found a few examples of how to chagne to source to a new database, but i have been unable to change that to what I need.

Currently the code fails at PivotSource = with object required.

any assistance provided would be helpful thanks in advance

Please Login or Register  to view this content.

[Andy Pope]

Can you not used named ranges to define the pivot data sources?

The you only need to update the named range and refresh the pivot table.

[sarails]

after spending "way too much time" on this

I have come up with the following code that accomplishes what Andy said I should do - use a defined name to change the data for the Pivot.

Whilst not pretty it does allow for what I need to happen, pivots change the data source based on the day of the week, as the sheet name changes every business day

Please Login or Register  to view this content.