how to alter the date within a macro
 

Hello,

I was trying to create an archive function on a workbook.
I have a formula throughout the workbook that is based on
the current date using NOW(). I went in a recorded a
macro of replacing the NOW() with last years date. How
can I set this so that I can have the user push a button
to archive the sheet and have the year based on the
current date? I want to change the replacement to an
argument that gives the year -1 at the time of clicking
the archive button. The current recorded code follows.

Thank You
LWhite

Cells.Replace What:="NOW()", Replacement:="Date
(2004,12,31)", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Application.CutCopyMode = False

How about something like:

Dim myStr As String
myStr = "date(" & Year(Date) - 1 & ",12, 31)"

Cells.Replace What:="NOW()", Replacement:=myStr, _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False, _
searchFormat:=False, ReplaceFormat:=False

LWhite wrote:

Hello,

I was trying to create an archive function on a workbook.
I have a formula throughout the workbook that is based on
the current date using NOW(). I went in a recorded a
macro of replacing the NOW() with last years date. How
can I set this so that I can have the user push a button
to archive the sheet and have the year based on the
current date? I want to change the replacement to an
argument that gives the year -1 at the time of clicking
the archive button. The current recorded code follows.

Thank You
LWhite

Cells.Replace What:="NOW()", Replacement:="Date
(2004,12,31)", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False
Application.CutCopyMode = False

--

Dave Peterson

Thank you very much. That really helped.

LWhite