Macro to change formulas

[govi]

Hi


I have a worksheet(02-01-2012) with formulas pointing to another worksheet: ='01-01-2012'!N10


I have made a macro which creates a copy of sheet 02-01-2012 and renames it to 03-01-2012.


How can I change the formulas in sheet 03-01-2012 so that they point to 02-01-2012?


I want to use this code so I can keep adding new worksheets.


Thanks,


govi

 

[Luke M]

Here's one idea...had to make a big assumption concerning worksheet layout, but you can probably use other methods for finding the names, if needed.

[pre]

Sub ChangeFormulas()
Dim OldName As String
Dim NewName As String

'Making an assumption that your worksheets are in order
'With the newer worksheets being to the right
'If older worksheets are to left, change the sign from - to +
OldName = Sheets(ActiveSheet.Index - 2).Name
NewName = Sheets(ActiveSheet.Index - 1).Name
ActiveSheet.Cells.Replace What:=OldName, Replacement:=NewName, LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False
End Sub

[/pre]

 

[govi]

Thank Luke M!

Your assumptions were right!