PDA

View Full Version : Renaming Defined Ranges



greymalkin
03-21-2008, 07:22 AM
Hello,

I am working with a group of worksheets that have several Name Defined ranges spread throughout (approx. 65). We now need to add a second copy of these worksheets for a slight variation, but all of the Name Defined Ranges will be in the exact same place. The problem is each Name Defined range must be unique. I was thinking the easiest way to do this and eliminate human error would be to copy the existing sheets to a new workbook, and use a loop to rename all the named ranges. The ranges are like this:

Destination_1
Destination_2
Destination_3
...
Destination_65

Source_1
Source_2
Source_3
...
Source_65

I created some code that would capture the name of the range, then create a new Name Defined range and assign it that range, but when I call on either the old or the new name it would still reference that range. If I then delete the old named range it deletes the new one as well. The problem is further compounded by the fact that these ranges have fomulas in them, otherwise I could simply select the range, delete it, then add a new range while it is still selected with the new name.

So...any ideas?

I basically need to rename Destination_1 to Destination_(1 + 65) and so on while maintaining the formula's etc. of the range.

Thanks for your help!

mdmackillop
03-21-2008, 07:34 AM
Can you post a sample workbook containing a couple of sheets with your named ranges?

greymalkin
03-21-2008, 08:13 AM
I could make an example sheet but unfortunately I can't post the original documents as they are proprietary. I'm basically just wanting to take a range named "Bob" for example and rename it to "Bob1" so that:
- All formatting and formulas remain intact
- If my Code references Bob1 it will go to this range
- If my code references Bob it will NOT go to this range as there will be another Bob in the workbook.

mdmackillop
03-21-2008, 08:22 AM
Try Sub CopySheetandUpdateNames()
ActiveSheet.Copy After:=Sheets(Sheets.Count)
ActiveSheet.Name = "Test"
For Each nm In ActiveSheet.Names
nm.Name = nm.Name & "_1"
Next
End Sub

Bob Phillips
03-21-2008, 09:06 AM
Jan Karel Pieterse has a utility designed to improve working with defined names. It is a free add-in, and you can get it at http://www.jkp-ads.com/OfficeMarketPlaceNM-EN.asp