Reassigning Range Names

Microsoft Excel 2003 (full product)
January 25, 2010 at 17:07:30
Specs: Windows XP
I have a range name (R1) which I used in various sheets to define lists(data validation).
I need to create several workbooks of the same contents, but each with different list entries for R1.
How can I have several range rames (r1a - r1z) but all renamed to R1 so I don't change my workbooks?

edited by moderator: Post moved from Windows XP Forum

See More: Reassigning Range Names

Report •

January 26, 2010 at 03:34:25
Range names can be changed with VBA, but obviously you can't have more than one range with the same name in a single workbook.

Please provide a little more detail and we'll see if we can help.

Do you have one workbook with a range named r1a and another workbook with a range named r1b, etc? In the end do you want these ranges to be named R1 in every workbook?

Report •

January 26, 2010 at 15:50:25
In workbook1.xls, I have defined range name R1 to use in several worksheets. I need to create several copies of Workbook1.xls, but with different cell references to R1. In the end, Workbook1.xls will have a range named R1 that will be assigned to the values of R1a and Worksheet2.xls will also have range R1 but assigned to the values of range R1b etc. In other words, the range R1 needs to be dynamically assigned values of R1a.....R1z to correspond to the worksheets.
Hope I have not confused you any further and appreciate any help. I'm very novice in VBA in excel.

Report •

January 26, 2010 at 16:49:42
re: "Hope I have not confused you any further"

You have.

One thing that is confusing me is your use of the words "workbooks" and "worksheets".

You use Workbook1.xls which is a file name and Worksheet2.xls which is also a file name, but slightly confusing since you change the naming structure. Then you say that R1 in each workbook has to be assigned values "to correspond to the worksheets".

Maybe it's me, but I'm not getting a clear picture. What worksheets, in what workbooks?

Just to be clear, I'll state something that may be obvious, but will at least get us to the same starting point:

A workbook is the term used for the Excel file itself, which must contain at least one worksheet. Of course, a workbook can contain more than one worksheet.

Maybe if you tried explaining it again, making very clear distinctions between workbooks and worksheets and how they relate to range names and data, I might get it.

For example...

"In Workbook1, Sheet1!A1:A5 is named R1. In Workbook2, Sheet1!B1:B5 is also named R1. I need..."

Report •

Related Solutions

January 26, 2010 at 17:20:03
Yes, I realise my error in the terms I have used and my sincere apologies. That said, here's the scenario and your example is a great starting point:

In Workbook1, Sheet1!A1:A5 is named R1. In Workbook2, Sheet1!B1:B5 is also named R1. I have defined separately range names r1-r10 on a seperate sheet in Workbook1. Based on a selection of a single range (r1-r10), R1's range gets updated in Workbook1, Workbook 2.....etc.
R1 is actually a drop down list, using data validation and that list needs to be changed to correspond to the workbook. i.e. Workbook1's R1 range := r1, Workbook2's R1 range =: r2 etc.

Report •

Ask Question