Automating Date Change in a macro

[yoitsmejy]

Hey guys, Is there any way to change the date within a macro code? I would like to automate the macro to update sfind and sreplace on a weekly basis. For example, for sfind change from 1/1/1900 to 5/2/2011 and sreplace from 5/2/2011 to 5/9/2011 at first, then every monday, it will update sfind and sreplace from 5/2/2011 to 5/9/2011...5/9/2011 to 5/16/2011 respectively. If possible automating it to run at like 5 am. Thanks.

[VBA]Sub CopyFormula()

Dim ws As Worksheet
Dim sfind As String
Dim sreplace As String

sfind = "1/1/1900"
sreplace = "5/2/2011"

For Each ws In Worksheets
ws.Activate
With ActiveSheet.Cells
Cells.Find(What:=sfind, After:=ActiveCell, LookIn:=xlFormulas, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
Selection.Copy
On Error Resume Next
Cells.Find(What:=sreplace, After:=ActiveCell, LookIn:=xlValues, _
LookAt:=xlPart, SearchOrder:=xlByColumns, SearchDirection:=xlNext, _
MatchCase:=True, SearchFormat:=False).Activate
ActiveSheet.Paste

End With

Next ws
End Sub[/VBA]

[Chabu]

define a Name in your workbook e.g. "sFind" and give it the value "1/1/1900"
in your vba code construct a reference to your workbook (e.g. "wb1")
[VBA]
Dim sFindDate As String
sFindDate = wb1.Names("sFind").Value[/VBA]

update the value with
[VBA]wb1.Names("sFind").Value = "1/1/2011"[/VBA]

it will be saved with your workbook