I have a workbook where the first worksheet (Sheet1) is kind of a template.
The user enters data there and some calculations occur and determine how many
hours should be coded for an employee for sick time, vacation, holiday and
short term disability etc.

What I am trying to have happen is:
User enters data on Sheet1 and hours are calculated on that same sheet.
A copy of that sheet is made and is named Payroll Period 1.
Sheet1 is cleared and ready for next week.
Next week the process repeats itself.
The tricky part is all those Payroll Period 1, Payroll Period 2 etc. sheets
stack up at the bottom of the workbook. I want to have a summary worksheet
that adds them up by week.
I know how to link together the formulas, but if I do before the worksheet
has been created, it References out in the formula on the Summary sheet, and
doesn't correct itself once the worksheet is created.
I tried creating all the Payroll Period sheets up front (max of 13) and link
them to eliminate this error. However, then the copy of Sheet1 needs to go
to an already existing worksheet rather than a new copy being made.
Anyone follow any of this?
Any ideas???

Thanks