macro help

[securityman]

I have a workbook that has several worksheets in it. I add a new sheet every Monday. The sheet is an exact copy of the pervious one, but I clear out certain cells, change the title of the sheet, and have to change some of the formulas.
I would like to create a macro to do all the changes for me after it copies the pervious sheet as a new one.

Example: 08-16-08 and 08-23-08 are the last 2 sheets that were added.
sheet 08-23-08 original title was 08-16-08 (2) so I manually changed it to 08-23-08. [I want the macro to do this for me]
this sheet also has formulas that pull some totals from sheet 08-16-08 . But when you copy the sheet it still pulls from 08-09-08 instead. ['08-09-08'!C4] because that is what 08-16-08 pulls from.
I need the macro to change the formula to '08-16-09'!C4.
there are several cells that would have to be changed when this new sheet is copied.

So I need the macro to copy the pervious sheet, change the tab title, change the formulas, and clear the cells that have to be cleared.
I have started the macro but there is a long list of cells in it that I would like to try to condense. The cells that need to be cleared and the cells that need the formulas changed are not together. They are separated by both rows and columns.

Any help would be appreciated!!!
I am on my way to work now and will not be back til tomorrow morning so I would not be able to answer any question until then.

Thanks.....

[Bob Phillips]

[vba]

Dim theDate As Date
Dim theSheet As Worksheet

theDate = Date - Weekday(Date, 2) - 8
On Error Resume Next
Set theSheet = Worksheets(Format(theDate, "yy-dd-mm"))
On Error GoTo 0
If theSheet Is Nothing Then

MsgBox "No sheet exists for date " & Format(theDate, "yy-dd-mm")
Else

theSheet.Copy after:=Worksheets(Worksheets.Count)
With ActiveSheet

.Name = Format(theDate + 7, "yy-dd-mm")
.Range("A1:A4").ClearContents
.Range("H5:H10").ClearContents
'etc
End With
End If
[/vba]

[securityman]

Sorry to get back in answering so late.

This did the job......Thanks, so much.