I use the attached workbook as my timesheet on a daily basis. The workbook contains a worksheet for each day I'm in the office. Everyday I copy the worksheet from the day before and rename it to that day's date in the "mm:dd:yy" format, I then change the data range of the pivot table in the new worksheet to be that of the new worksheet. Also, the attached workbook includes small code that updates the active pivot table when the data in the worksheet changes.
I would like to automate this entire process and this is why I'm turning to you for help. I would like to accomplish the following when clicking the "Create New Worksheet" button:
1. Copy Active Worksheet
Copy the active worksheet, or in this case the "09-08-11" worksheet, and paste it to the end, which in my workbook would be after "09-08-11" worksheet, since it only contains one worksheet
2. Rename the newly copied worksheet
This newly copied worksheet should be renamed to today's date, "09-09-11", using the "mm:dd:yy" format.
3. Change Pivot Table Data Range
The pivot table in the newly copied worksheet has its data range tied to the source worksheet "09-08-11", so this data range should be changed to the same range in the "09-09-11" worksheet.
I attempted to accomplish this, but I ran into problems where my code was using the cached data range, specifically pointing to a location in my computer.
Thanks much for your help.