Changing links for next months spreadsheet

[Ted Dutter]

Hi,

I am creating an income statement spreadsheet. This
spreadsheet refers to the previous month's data for a
comparison. I would like to create button, such as "Add a
New Month", that would run either a macro or code that
would create a new spreadsheet and change the links to
point to the last month.

I know how to create the button and create the new
spreadsheet with the new name, but how to automatically
change the links is stumping me.

For example, a new spreadsheet named "Income Statement 08
2003" would be changed to get its links from "Income
Statement 07 2003". I have been successful doing it
manually by searching and replacing "[Income Statement 06
2003]" with "[Income Statement 07 2003]" but I would like
to automate the process so my client can't goof anything
up.

Any help or suggestions would be greatly appreciated.

Thanks,
Ted Dutter

 

[Steve Smallman]

Ted,

If you know how to write the code to add a new sheet, then add

Cells.Replace What:=prevref, Replacement:=newref, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False

I have used variables prevref and newref to refer to the sheet names. you
will need to define these in the code prior to execution of this line.
prevref is the old sheet name reference you wish to replace, and newref is
the current worksheet name. I have assumed you have defined these or can
define these elsewhere in your code.

Steve