Incrementing named cells

[Justin]

Merry Christmas everyone!!!

Was wondering if any of you can help me with this. I have
a financial model that currently has numerous named cells
in the format of XXX_03 ( the 03 is to denote 2003 ) but I
am making 2004 projection and therefore am wondering if
there was an easy way to sort of copy and paste the names
into a new column and automically renaming cells by an
increment of 1 to XXX_04. If anyone can help, it will be
greatly appreciated!

Thank you

Justin

 

[Don Guillett]

See one answer 1 minute later

 

[Bob Phillips]

Justin,

How about something like

Sub ConvertNames(yr As Long)
Dim nme As Name

For Each nme In ActiveWorkbook.Names
If Right(nme.Name, 3) = "_" & Right(CStr(yr), 2) Then
ActiveWorkbook.Names.Add Name:=Left(nme.Name, Len(nme.Name) - 2)
& Right(CStr(yr + 1), 2), _
RefersTo:=nme.RefersTo
nme.Delete
End If
Next nme

End Sub


Call with
ConvertNames(2003)

or

ConvertNames(Year(Date))

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)