If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Identical Name on multiple workbook pages
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 |
#2
|
|||
|
|||
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 |
#3
|
|||
|
|||
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 |
#4
|
|||
|
|||
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 |
#5
|
|||
|
|||
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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Printing Multiple Pages When One Is Needed | Chuck | Setting Up & Running Reports | 0 | March 22nd, 2005 12:33 AM |
Printing Selected Pages from a Document Containing Multiple Page Numbering Schemes | Don | Formatting Long Documents | 7 | January 14th, 2005 03:07 AM |
Printing Selected Pages from a Document Containing Multiple Page Numbering Schemes | Don | Page Layout | 5 | January 14th, 2005 03:07 AM |
Can't get it to print different multiple pages | Markis Gardner | Publisher | 2 | May 10th, 2004 04:20 AM |
link multiple cells from other workbook on another machine on network | Bryce | Links and Linking | 2 | October 13th, 2003 04:58 PM |