a macro that will change the file names within it after running

[Gai]

I have made a rode for my own back. I have developed a very large excel
file(s) which, depending on the file, will have up to 178 worksheets within
it. The sheets are protected individually because the people entering data
into the sheets have little idea of excel and there is a lot of formula and
hyperlinks.

Each six weeks I have to archive the file to another location, give it a new
name, put a new blank template in the original location, unprotect the second
file, update the links it refers to to the new name of the file I just
created (this is because parts of the file refers to the last period data).
Some of the relating code is below:

ActiveWorkbook.SaveAs Filename:= _
"P:\unit\fmc\archive\6 wkly 21102006.xls", FileFormat:=xlNormal, _
Password:="", WriteResPassword:="", ReadOnlyRecommended:=False, _
CreateBackup:=False

Sheets("Files").Select Replace:=False
Cells.Select
Range("F1:L80").Activate
Selection.Replace What:="dummyFile", Replacement:="21102006", LookAt:= _
xlPart, SearchOrder:=xlByRows, MatchCase:=False,
SearchFormat:=False, _
ReplaceFormat:=False

What I have to do presently is remember to go to each file at some point
during their six week cycle to change the macro manually to the next date. ie
do a "replace" in visual basic for 21102006 and replace to 02122006, and the
next month find 02122006 and replace with 13012007, and so on. I have
problems relying on my memory!!

What I want is to be able to run something at the end of the macro, or in a
separate but relating macro to do this for me after the archiving macro has
run. I have searched the bowels of the discussion group and have yet not
found anything, but bare in mind- I am still learning!

Cheers

 

[NickHK]

Dim PathFilename as string
PathFilename="P:\unit\fmc\archive\6 wkly " & Format(now(),"ddmmyyyy") &
".xls"

ActiveWorkbook.SaveAs Filename:= PathFilename, FileFormat:=xlNormal, _

If the date in the file name is not based on now (that day), then:
- Separate the date from the current filename
- Add the correct number of days/weeks using DateAdd
- Use that date in place of Now() in the above.

NickHK