Define Names in Excel

D

Dave T at home

Insert -> I have a workbook for each month of the year. I have named cells
in the workbook e.g. DT_Inc_Jan_Pupil_Hourly_Cash and DT_Exp_Jan_Fuel_Cash.

I have pasted the names into a workbook.

I would like to copy these NAMES and do a replace from '_Jan_' to '_FEB_'
etc as I need an entry for each month and then Define them in Names.

Can this be done easily without adding each entry in Insert -> Name ->
Define or can someone help me please with an easier / better method?

Thanks

Dave
 
B

Bob Phillips

Dave,

VBA OK?

Sub UpdateNames()
Dim nme As Name

For Each nme In ActiveWorkbook.Names
If nme.Name Like "*Jan*" Then
Names.Add Name:=Replace(nme.Name, "Jan", "Feb"),
RefersTo:=nme.RefersTo
nme.Delete
End If
Next nme
End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)
 
D

Dave T at home

Thanks for youur help Bob.

I have also found NamesManager created by Jan Karel Pieterse and others
which does everything (and more) that I wanted at
http://www.decisionmodels.com/downloads.htm

I have now used it for a couple of days and found it very helpful as I now
have 788 names in my excel file.

Thanks again for your help.

Dave
 
Top