MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Naming ranges

Posted by Lori D on October 15, 2001 12:38 PM

How can I (is there a way) name a range in my worksheet, but have the same name for the range, for each page of the workbook. There are 5 worksheets in my workbook, set up identically: Monday thru Friday. I want cells b2:k126 to be named Data_Range for each worksheet, so my macro can call it no matter which worksheet I'm on. Is there a way to do this?

Posted by Aladin Akyurek on October 15, 2001 12:46 PM

No. Names must refer to unique ranges or cells.


Posted by Jonathan on October 15, 2001 2:49 PM

Yes, you can do this. Here's the technique:

When you name the range use Insert/Name/Define (also accessible with Ctrl + F3). Name your range WITH the sheet name, like Sheet3!East . The 'Sheet3!' part will not appear in the range box, but it will allow you to call East from Sheet3 and get the East ON Sheet3. You can do the same with the other sheets: you could have an East on each one, and it could occupy the same (or different) cells on each sheet, but as long as you add the Sheet name to the Name field -- I'm not talking about the RefersTo field which is below on the dialog box -- each sheet will have its own range with the same name, e.g. "East". If I wasn't clear, I'll try again.

Posted by LoriD on October 16, 2001 4:33 AM

That worked great! Thanks.