Hi all, tearing my hair out trying to find a way to dynamically change a pivot tables data source.
I have a workbook with numerous worksheets.
A new worksheet is added every week and my last worksheet contains my pivot tables.
Each worksheet has the same sort of data, and I manually define named ranges in each worksheet that I then use in the pivot tables.
i.e. each weeks worksheet will named as ddmmyy and will contain a named range called _ddmmyy_range1 where ddmmyy is the day, month and year.
So then by manually changing the pivot table data source range I can point to the relevant worksheet and named range.

So what I am trying to do is on the pivot table worksheet, is to have a normal cell (say cell a1) that I can change the value to of the defined named range so the pivot table changes. In summary, I am trying to find a way to get INDIRECT(a1) to work as the pivot table data source.

Any pointer would really be appreciated.
Howard