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!
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!