Update Pivot Table with New Sheets

[WRPCWill]

I'm attaching the file that I need help with. When you open the file, it displays a dialog asking if you want to "View Data" or "Begin New Audit." If you click "View Data," it closes the dialog box. If you click, "Begin New Audit," it creates a new sheet based on a Master Sheet, and it names the new sheet based on the current date (in yyyymmdd format). The workbook contains a pivot table that needs to update with information from the new sheet. I can update the pivot table manually (I'm using multiple consolidation ranges), but I need to create a button that updates the table with the ranges from the new sheet when clicked.

I know the answer probably lies in using named ranges, but I don't know how to use named ranges with new worksheets. Please help!

[Mallycat]

what version of excel are you using? Are you are able to convert it (or let someone else convert it) to Excel 2003? But you will need to post some sample data to show how the consolidation works.

[WRPCWill]

I'm using Excel 2007. I posted the spreadsheet in the original post; do you need more? If I convert it to 2003, what do I do then? FYI, this will be used on many different computers all running Excel 2007.

[Mallycat]

I can't help you given it is Excel 2007 (sorry, I only have 2003) and it would not be a good idea to backward convert.