A very trick and confusing excel question

[webking]

Its a very tricky and confusing thing iam about to ask.
If i place the following vba code in modulue of the sheet iam using in excel, it creates a new similar sheet automatically when ever i open that particular file and this sheet is create according to the present date and day on which it is opened.
Now this sheet contains information about daily expenses , production store , marketing store etc..
Now i want to make a weekly report of daily sheets.
Can some how any one can modify the following code in such a way that when ever a new sheet is opened the cells from which i want to transfer data to the weekly file automatically become linked.

Like suppose if i want that on March 12 the A1,B1,C1 columns data should be transfered to weekly file columns A1,B1,C1, then on the next day when a new sheet on a new day comes in act due to the following code, the link shifts in such a way that on March 13 the A1,B1,C1 columns data tranfesr to the A2,B2,C2 columns of weekly report. As in weekly report the cellsA1,B1 and C1 are filled with March 12 data.


Option Explicit

Public Sub auto_open()

Dim newSheet As Worksheet
Dim yestSheet As Worksheet
Dim temp As Date
Dim tempDate As Date
On Error Resume Next
yestSheet = ActiveWorkbook.Worksheets(Format$(Date, "yyyy-mm-dd"))
If (Err.Number = 0) Then
Exit Sub
End If

tempDate = 0
On Error Resume Next
For Each yestSheet In ActiveWorkbook.Worksheets
temp = CDate(yestSheet.Name)
If (Err.Number = 0) Then
If (tempDate < temp) Then
tempDate = temp
End If
End If
Err.Clear
Next yestSheet
On Error GoTo 0
Set yestSheet = ActiveWorkbook.Worksheets("Draft")
Set newSheet = ActiveWorkbook.Worksheets.Add(yestSheet)
yestSheet.Cells.Copy newSheet.Cells
newSheet.Name = Format$(Date, "yyyy-mm-dd")
If (tempDate > 0) Then
Set yestSheet = ActiveWorkbook.Worksheets(Format$(tempDate, "yyyy-mm-dd"))
yestSheet.Range("E9:E28").Copy
newSheet.Range("B9:B28").PasteSpecial xlPasteValues
yestSheet.Range("K9:K28").Copy
newSheet.Range("H9:H28").PasteSpecial xlPasteValues
End If
newSheet.Range("C1").Value = Format(Date, "Short Date")
newSheet.Range("E1").Value = Format$(Date, "dddd")
Set newSheet = Nothing
Set yestSheet = Nothing
End Sub