If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Editing large numbers of cell names
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. |
#2
|
|||
|
|||
Editing large numbers of cell names
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. |
#3
|
|||
|
|||
Editing large numbers of cell names
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. |
#4
|
|||
|
|||
Editing large numbers of cell names
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. |
Thread Tools | |
Display Modes | |
|
|