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.

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
---------------
Jacob Skaria


"Al" wrote:

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.

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" wrote:

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
---------------
Jacob Skaria


"Al" wrote:

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.

You posted the same question yesterday and I responded

If this post helps click Yes
---------------
Jacob Skaria


"Al" wrote:

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" wrote:

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
---------------
Jacob Skaria


"Al" wrote:

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.