HOW TO: One name range to work for multiple sheets
 

Kevin Past this into an empty vba module
Sub RSelect()
Dim MyRange As Range
Set MyRange = ActiveSheet.Range("A1:A10")
MyRange.Select
End Sub

Then past this into the code window for each "Sheet" Object
Private Sub Worksheet_Activate()
RSelect
End Sub

Hope this helps.
Dan Thompson

"Kevin McCartney" wrote:

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