As a hardened Excel/VBA nut, I'm probably going to be really embarrased when
I get the answer to this, but here goes...

I have a workbook containing a worksheet that records weekly data. I have a
named range ("Data") which I select then clear, via GoTo.
Each week, I copy the latest week's worksheet and create a new worksheet for
the new week. And each week, I select the range "Data" and clear it in the
most recently created worksheet.

the thing is, I always thought that Range names were unique across a
workbook, and that you couldn't have the same Range Name in more than one
worksheet.
Yet each week, I create a new worksheet, GoTo "Data" (which always selects
the correct range in the active worksheet) to create a new template for the
new week.

Excelers, MVPs and the whole of the Western hemisphere will probably be
slapping their heads in disbelief at the dumbness of this question (I have
green ticks for helping people on here, would you believe..?) If I wanted to
refer to "Data" in VBA, I suppose I'd have to qualify it with a worksheet
name.

Can anyone put me straight as to what the rules are?

Thanks (in a terminally bewildered "What's my name again?" sort of way)

Pete

Peter,

You are correct that you can have a range "Data" in several worksheets and
since that is possible in order to access those ranges in VBA you will have
to specify which worksheet the range "Data" you are trying to reference.

For example:

Sheets("Sheet1").Application.Goto Reference:="Data"

I hope this answers your question.



"Peter Rooney" wrote:

As a hardened Excel/VBA nut, I'm probably going to be really embarrased when
I get the answer to this, but here goes...

I have a workbook containing a worksheet that records weekly data. I have a
named range ("Data") which I select then clear, via GoTo.
Each week, I copy the latest week's worksheet and create a new worksheet for
the new week. And each week, I select the range "Data" and clear it in the
most recently created worksheet.

the thing is, I always thought that Range names were unique across a
workbook, and that you couldn't have the same Range Name in more than one
worksheet.
Yet each week, I create a new worksheet, GoTo "Data" (which always selects
the correct range in the active worksheet) to create a new template for the
new week.

Excelers, MVPs and the whole of the Western hemisphere will probably be
slapping their heads in disbelief at the dumbness of this question (I have
green ticks for helping people on here, would you believe..?) If I wanted to
refer to "Data" in VBA, I suppose I'd have to qualify it with a worksheet
name.

Can anyone put me straight as to what the rules are?

Thanks (in a terminally bewildered "What's my name again?" sort of way)

Pete

David,

Thanks for taking the time to answer this question.

At least you didn't laugh...

Pete

"David Hepner" wrote:

Peter,

You are correct that you can have a range "Data" in several worksheets and
since that is possible in order to access those ranges in VBA you will have
to specify which worksheet the range "Data" you are trying to reference.

For example:

Sheets("Sheet1").Application.Goto Reference:="Data"

I hope this answers your question.



"Peter Rooney" wrote:

As a hardened Excel/VBA nut, I'm probably going to be really embarrased when
I get the answer to this, but here goes...

I have a workbook containing a worksheet that records weekly data. I have a
named range ("Data") which I select then clear, via GoTo.
Each week, I copy the latest week's worksheet and create a new worksheet for
the new week. And each week, I select the range "Data" and clear it in the
most recently created worksheet.

the thing is, I always thought that Range names were unique across a
workbook, and that you couldn't have the same Range Name in more than one
worksheet.
Yet each week, I create a new worksheet, GoTo "Data" (which always selects
the correct range in the active worksheet) to create a new template for the
new week.

Excelers, MVPs and the whole of the Western hemisphere will probably be
slapping their heads in disbelief at the dumbness of this question (I have
green ticks for helping people on here, would you believe..?) If I wanted to
refer to "Data" in VBA, I suppose I'd have to qualify it with a worksheet
name.

Can anyone put me straight as to what the rules are?

Thanks (in a terminally bewildered "What's my name again?" sort of way)

Pete

Pete,

There are global names, and there are local names.

If you wanted to clear one sheet's "Data" you could use
Worksheets("Sheet Name").Range("Data").ClearContents

To clear all sheets' Data range then use

Sub TryNow()
Dim mysht As Worksheet
For Each mysht In ThisWorkbook.Worksheets
mysht.Range("Data").ClearContents
Next
End Sub


To create a global name, select your range, and type the new name in the name box. To create a
local name, when typing in the name, precede the name by the sheet name, like

SheetName!Data

Or, if your sheet name has a space:
'Sheet Name'!Data

When you press enter, the name will switch to just Data, but it will refer to the local range.

Might I suggest that you get a copy of Jan Karel Pieterse's (with Charles Williams and Matthew
Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

HTH,
Bernie
MS Excel MVP


"Peter Rooney" wrote in message
...
As a hardened Excel/VBA nut, I'm probably going to be really embarrased when
I get the answer to this, but here goes...

I have a workbook containing a worksheet that records weekly data. I have a
named range ("Data") which I select then clear, via GoTo.
Each week, I copy the latest week's worksheet and create a new worksheet for
the new week. And each week, I select the range "Data" and clear it in the
most recently created worksheet.

the thing is, I always thought that Range names were unique across a
workbook, and that you couldn't have the same Range Name in more than one
worksheet.
Yet each week, I create a new worksheet, GoTo "Data" (which always selects
the correct range in the active worksheet) to create a new template for the
new week.

Excelers, MVPs and the whole of the Western hemisphere will probably be
slapping their heads in disbelief at the dumbness of this question (I have
green ticks for helping people on here, would you believe..?) If I wanted to
refer to "Data" in VBA, I suppose I'd have to qualify it with a worksheet
name.

Can anyone put me straight as to what the rules are?

Thanks (in a terminally bewildered "What's my name again?" sort of way)

Pete

Bernie,

The bit about prefixing the name with the sheetname was VERY helpful.
I already have a copy of the Name Manager, but now it makes a whole lot more
sense.

Thanks for your help :-)

Pete

"Bernie Deitrick" wrote:

Pete,

There are global names, and there are local names.

If you wanted to clear one sheet's "Data" you could use
Worksheets("Sheet Name").Range("Data").ClearContents

To clear all sheets' Data range then use

Sub TryNow()
Dim mysht As Worksheet
For Each mysht In ThisWorkbook.Worksheets
mysht.Range("Data").ClearContents
Next
End Sub


To create a global name, select your range, and type the new name in the name box. To create a
local name, when typing in the name, precede the name by the sheet name, like

SheetName!Data

Or, if your sheet name has a space:
'Sheet Name'!Data

When you press enter, the name will switch to just Data, but it will refer to the local range.

Might I suggest that you get a copy of Jan Karel Pieterse's (with Charles Williams and Matthew
Henson) Name Manager:

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

HTH,
Bernie
MS Excel MVP


"Peter Rooney" wrote in message
...
As a hardened Excel/VBA nut, I'm probably going to be really embarrased when
I get the answer to this, but here goes...

I have a workbook containing a worksheet that records weekly data. I have a
named range ("Data") which I select then clear, via GoTo.
Each week, I copy the latest week's worksheet and create a new worksheet for
the new week. And each week, I select the range "Data" and clear it in the
most recently created worksheet.

the thing is, I always thought that Range names were unique across a
workbook, and that you couldn't have the same Range Name in more than one
worksheet.
Yet each week, I create a new worksheet, GoTo "Data" (which always selects
the correct range in the active worksheet) to create a new template for the
new week.

Excelers, MVPs and the whole of the Western hemisphere will probably be
slapping their heads in disbelief at the dumbness of this question (I have
green ticks for helping people on here, would you believe..?) If I wanted to
refer to "Data" in VBA, I suppose I'd have to qualify it with a worksheet
name.

Can anyone put me straight as to what the rules are?

Thanks (in a terminally bewildered "What's my name again?" sort of way)

Pete