Editing cell names

[Al]

Is there any means of editing a vast number of cell names within a
spreadsheet without having to resort to Insert - Name - Define and editing
each one separately?

I have a spreadsheet with 100s of cells names across the spreadsheet. I now
wish to expand the function of my spreadsheet to be able to handle, say, 3
projects within the same spreadsheet, and intend having, say, "price_A",
"price_B" and "price_C" in separate worksheets.

I am thinking that I have to proceed as follows:
1- starting with template spreadsheet, edit all names to read "cell name_A",
and save file as "new multiple project spreadsheet."
2 - revert to original template, edit all names to read "cell name_B" and
copy that worksheet into the "new multiple project spreadsheet."
3 - repeat 2 for "cell name_C."

I will end up with a spreadsheet with three cells named "Total," however
these will be Total_A, Total_B and TotalC.

If there's no quick way, then I have to rename all one at a time!

Using Excel 2003 on XP Professional 2002

 

[Jacob Skaria]

Use the below macro to rename all the named ranges at one shot...The below
macro would get the sheet name for each named range and if it is SHEET1 then
add a "_A" to the current name "_B" if the range is referred to Sheet2 and so
on..

--Edit the sheetnames to suit your requirement
--Note that the sheetnames are in upper case

If you are new to macros..

--Set the Security level to low/medium in (Tools|Macro|Security).
--From workbook launch VBE using short-key Alt+F11.
--From menu 'Insert' a module and paste the below code.
--Get back to Workbook.
--Run macro from Tools|Macro|Run <selected macro()>


Sub Macro()
Dim varName As Name
For Each varName In ActiveWorkbook.Names
Select Case UCase(Mid(Split(varName.RefersTo, "!")(0), 2))
Case "SHEET1"
varName.Name = varName.Name & "_A"
Case "SHEET2"
varName.Name = varName.Name & "_B"
Case "SHEET5"
varName.Name = varName.Name & "_C"
End Select
Next
End Sub

If this post helps click Yes