HOW TO: One name range to work for multiple sheets
 

HI TWIMC,

Is there a way to enter one NAMED range that will work on all sheets in a
workbook? E.g. I'd like to be able to set the range A1:A10 as MyRange on
Sheet1 and I when I select Sheet2, and select MyRange, I want range A1:A10 on
Sheet2 to be selected and not A1:A10 on Sheet1.

Now I know I can select each sheet in turn and select the same range and
type in the NAME box sheetname!MyRange but it's a bit to much manual work for
a workbook with over 50 sheets and potentially the range required can change
shape per workbook, (not sheet).

Any ideas, much appreciated

TIA
KM