Editing large numbers of cell names

[Al]

I have 100s of cell names withing my spreadsheet. Is there a more efficient
way of editing these rather than one at a time (Insert - Name - Define)

Office Professional 2003.

 

[Jacob Skaria]

What happened to your previous post...

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

 

[Al]

Yes, I am new to macros, so I will experiment as per your suggestion...thank
you very much!

What did you mean by "What happened to your previous post.....?"

 

[Jacob Skaria]

You posted the same question yesterday and I responded

If this post helps click Yes

 

[Al]

This is my 2nd response to your answer....

"An error occurred while sending your pos
--------------------------------------------------------------------------------

We're sorry, but there was a problem with the system and your post was not
received. The error has been reported to Operations and will be investigated
as soon as possible. Please try again later."
I got this same response to my first post in this chain...quite a frequent
occurrence!