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


0
PeterRooney (105)
9/15/2005 2:07:05 PM
excel.misc 78881 articles. 5 followers. Follow

4 Replies
430 Views

Similar Articles

[PageSpeed] 30

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
> 
> 
0
9/15/2005 2:37:04 PM
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
> > 
> > 
0
PeterRooney (105)
9/15/2005 2:45:27 PM
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" <PeterRooney@discussions.microsoft.com> wrote in message 
news:66B231E8-0CC9-4FB4-AAF7-799645D03E38@microsoft.com...
> 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
>
> 


0
Bernie
9/15/2005 2:59:43 PM
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" <PeterRooney@discussions.microsoft.com> wrote in message 
> news:66B231E8-0CC9-4FB4-AAF7-799645D03E38@microsoft.com...
> > 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
> >
> > 
> 
> 
> 
0
PeterRooney (105)
9/15/2005 3:28:08 PM
Reply:

Similar Artilces:

Linking Charts to publisher file saved as web page
Good Day I hope someone can help me with this question - I am using publisher 2007 to create a newsletter ( Saved as web page ) on our local intarnet. I am now trying to add / link charts and excel tables to the newsletter. I am only able to paste as picture and have them dynamically update with daily sales for example. Can anyone help me with this . . . I am trying to find a way to insert excel charts and tables into my publisher 2007 webpage and have them automatically update when the excel files update - I do not want to copy and paste and re-publish everyday ...

Size of workbook
My workbook is indicating that its size is 7 Mb and I do not know why. As far as I can make out it should be less than 1Mb Hi Try resetting the used range. Have a look here for info: http://www.contextures.com/xlfaqApp.html#Unused -- Andy. "Anthony Byrne" <AnthonyByrne@discussions.microsoft.com> wrote in message news:F0EA31D2-9A5E-48E0-AFF6-FD2F5C5D3137@microsoft.com... > My workbook is indicating that its size is 7 Mb and I do not know why. As far > as I can make out it should be less than 1Mb ...

Exchange 2003 sends multiple emails
I just discovered that when a user sent email to a specific distribution list or group of people, the email was recieved by the recipients over four times. I can see in the logs that the Exchange server did send roughly this many, but I dont know why. any ideas? thanks ...

Multiple Mail Messages & File Attachments Displayed
I have Outlook 2003 SP2. Periodically emails will arrive twice. In the message template, my email address will be displayed many times in the To: field and any associated attachment(s) will be displayed multiple times. Any assistance is appreciated. ...

Formatting tabs for pages and subpages
Is there a way to format the appearance of the tabs for pages and subpages? I'd like to use specific colors for different pages and groups. Also, is there a way to increase the size of the tabs either horizontally or vertically to accommodate a longer, more descriptive label. On 19/02/2010 2:29 AM, hmbsport wrote: > Is there a way to format the appearance of the tabs for pages and subpages? > I'd like to use specific colors for different pages and groups. Unfortunately, no. Also, is > there a way to increase the size of the tabs either horizontally or > ...

create an index listing where specific names are mentioned
I am writing a history about my Dad and Mom. I want to create an index showing the pages specific names are mentioned. How do I do this. Publisher does not have an Index feature. Use Word. -- Mary Sauer http://msauer.mvps.org/ "family history mama" <family history mama@discussions.microsoft.com> wrote in message news:BFF1FE4A-E80F-444F-8E7D-6AA71878D100@microsoft.com... >I am writing a history about my Dad and Mom. I want to create an index > showing the pages specific names are mentioned. How do I do this. ...

display sums from multiple worksheets on one worksheet
I am working on a budget plan. So I have multiple totals from various months that I would like to display on one "totals" page so i can track each month. for example, I have the totals from bikes, cars, and transit on the november worksheet. on the "totals" page I have the totals from bikes, cars, and transit for every month of the year. i kept the names consistent from each page hoping that would simplify things. so far it has not. is there a way to display the totals from each month on the "totals" without having to type a similar formula in eac...

Conditional formatting
Hi Excel Experts I am creating several workbooks, one will be mine, and the others linked to mine and sent to people for them to revise and return to me to enable mine to automatically update, (the linked cells will contain blanks or numbers). Please could you tell me if there is a formula I could use in conditional formatting to enable me to identify in my workbook which linked cells will have changed, i.e. when a number is revised or added to blank cell the font colour changes , or if the number is deleted the blank cell changes colour? I hope I have explained myself correctly Man...

Word Printing and Rotation of Landscaped Pages
When printing a document with mostly portrait pages but some landscapes, the landscape page is rotated for printing and is rotated so that the page top is on the right hand side of a traditional portrait page. I want it to rotate 180 degrees from that so that the bottom of the landscape page is on the right hand edge of the portrait page. Cannot seem to find a place to do that. Help !!! I don't think Excel can do that. Mike wrote: > When printing a document with mostly portrait pages but some landscapes, the > landscape page is rotated for printing and is rotated ...

One IP-address
Our exchange 2003-server hosts two domains A.com and B.com. So, the mx-record for BOTH domains point to the same host (mailhost.A.com) and resolves the corresponding IP-addres. The PTR-record for this IP-address obviously points back to the host mailhost.A.com. All seems to work fine for both domains for the moment, but will this not present a problem in the future for mail from the B.com domain when more and more receiving hosts do reverse lookup ? Or is reverse lookup just verifying the FQDN in the mail-header, and not the senders domain ? Regards Rudy Steyaert HI, NO i don't think ...

Can't customize the Outlook Today Page
I ma having trouble customizing the "OutLook Today" page in outlook 2000. Everytime I click on the Costomize Outlook Today, nothing happens, I can drag it to the desktop, it will open then, but it won't save any of the settings. I have removed and loaded outlook and still nothing. I have tried running the repair option on office and that doesn't work either. Any suggestions would be greatly appreciated. This is happening under both XP and Win98 SE. I believe all update have been added. -- Regards, Bob Frost Canterbury House Bed and Breakfast 802.457.3077 Bob <bo...

Printing just one page?
I would like to print just one page in my publication, at times. When I hit file, print, I do not have a print option. Print merge, print preview are all that is available. Can anyone help? Please?? What version Publisher? When you are ready to print one page, there should be check off for "current page". Are you expanding the File menu? Right-click any toolbar (Publisher 2002), customize, toolbars tab, check *Always show full menus." What happens if you ctrl+p or click the print icon on the toolbar? -- Mary Sauer MSFT MVP http://office.microsoft.com/ http://msau...

Two identical formulas for a different result?
Hello everyone, :) A very quick question, but nonetheless very intriguing to me (I jus lost 4h on that :( ) I don't understand why my first formula works, and the second and thir don't. {=SUM(IF(FiscYear="2003",IF(IncomeFeeID="RB",Amount,0),0))} =SUMPRODUCT((FiscYear="2003")*(IncomeFeeID="RB")*Amount) (result is #value) =SUMPRODUCT((FiscYear="2003")*(IncomeFeeID="RB"),Amount) (this one could work too I thought, but result in a 0) I am simply trying to sum (amount) when the fiscal year is 2003 an IncomeFeeId is RB......

Multiple charts with same serie names
I want to have the same color in all four of my line charts per series. All charts have the same series name. Is there some type of VBA code that I can run a macro to achieve this ? example: boat = green line mnotorcycle = yellow line auto = red line Hi, You could avoid VBA, maybe, by choosing Tools, Options, Color and setting the first three colors of the Chart fills aor Chart lines area depending on the type of charts you are using. -- If this helps, please click the Yes button Cheers, Shane Devenshire "Jennifer" wrote: > I want to have the same color in all four of m...

create defined name using variable file name in reference
please help in create defined name EXAMPLe: create name : ACCPath ---- Referes to (='E:\Data Folder\[externa work book.xlsx]sheet1'!$K$8:$K$10000) this works I NEED to put (external work book.xlsx) in a cell and refere to it as variable in the referes to (='E:\Data Folder\[&U2&]sheet1'!$K$8:$K$10000) where U2 having the file name because it is changing meny times Activeworkbook.Names.Add Name:="ACCPath", RefersTo:= "=" & Activesheet.Range("U2").Value2 --- HTH Bob Phillips "hala" <hala@discussions.mi...

How to print header all page
I create assetNo header (difference number of record for each assetNo) in my report and set keep together for each assetNo. If data in any assetNo more than one page, My report does not print assetNo header on next page. Where am I wrong. Nova wrote: >I create assetNo header (difference number of record for each assetNo) in my >report and set keep together for each assetNo. >If data in any assetNo more than one page, My report does not print assetNo >header on next page. To get a group header to appear on each page in addition to the start of the group, you need to set t...

If...ElseIf...End If with multiple conditions
I am learning VBA for Access. On the form I have Text Box "Cat" and Combo Box "CatCb". CatCb data source has 6 values including "Dr", "Pt" and the last value "Undefined". I have class module as follows Private Sub CatCb_AfterUpdate() If Nz(Cat.Value) = "" Then Cat.Value = CatCb ElseIf Cat.Value <> "Undefined" And CatCb = "Undefined" And MsgBox("Replace all current Categories with 'Undefined'?", vbYesNo, "Categories") = vbYes Then Cat.Value = CatCb Else ...

Changing Multiple Users Details
We are using Exchange 2003 SP2 and I need to change all our users company info and addresses, as we have moved physical locations... Is there a way to change all users information in one simple procedure? Not sure if this is an Exchange thing or a AD thing... Thanks in advance guys... I think ADModify.net might do what you want http://www.gotdotnet.com/Workspaces/Workspace.aspx?id=f5cbbfa9-e46b-4a7a-8ed8-3e44523f32e2 "Chris H" <ChrisH@discussions.microsoft.com> wrote in message news:76A45444-7337-4C50-B536-34C4F6F9EBA5@microsoft.com... > We are using Exchange 2003...

Multiple subtables
Hello, Not sure how to approach this: We have an inventory database that has a variety of item types. Workstations and printers have quite a difference of data fields to be recorded. Should we store all items in one table and just not use the fields that do not pertain to those types or should we have a parent table with the shared fields and multiple subtables to store the specific fields to each type? The second sounds like a better idea but couldnt things go wrong later with multiple tables linking to the same parent table? Thanks, Ernst. Do an analysis on w...

Multiple Front End Users
Creating a database for scheduling senior citizen transportation. The database will reside on the network so all three can use. Two or more staff members will need to add, edit, entries to a table throughout the day. Two or more staff members will also need to run a report to show what has been scheduled throughout the day at moment's notice to be sure they are not overscheduling. Can one or more persons work in the same table at the same time? When entries are added or changed will all three staff members see the most current data if the table is open? Can the report b...

Emailing daily agenda to multiple people
After much searching I've not been able to find any ability to automate sending a daily email with my agenda on it to multiple recipients. Previously I've used Google Calendar tool to sync OL2007 with Google Calendar and it sends out a daily agenda, similar to the following: Tue Nov 17, 2009 All day Tue Nov 17 – Fri Nov 20 Marking Exams (Office) 09:00 – 10:00 Call Margaret Carney 18:00 – 21:00 Pacioli Society Meeting but as I am currently trialling OK2010 beta the google sync tool no longer works. But this was a clumsy way of achieving the above result anyway. Any...

In Excel, how can I print a long list on 1 page in multiple colum.
I have a long (700 rows) list in an Excel worksheeet. I would like to print the whole list on one page by making the font smaller and by printing it on multiple columns on the page. How can I set this up? Thanks Check out this David McRitchie link for the columns question: http://www.mvps.org/dmcritchie/excel/snakecol.htm "Chuck" wrote: > I have a long (700 rows) list in an Excel worksheeet. I would like to print > the whole list on one page by making the font smaller and by printing it on > multiple columns on the page. How can I set this up? > > Thanks ...

convert a string into a textbox name
Trying to loop to load different textbox Textbox1, Textbox2, Textbox3 ect n = 1 xCt = 15 xText = 35 Do while xCt > 0 Textbox(n) = xText n = n + 1 xCt = xCt - 1 xText = xText + 3 Loop It would help you get a helpful answer if you provided... Excel version? Where the code is located? Where the textboxes are located What kind of textboxes are they (or how you created them)? What is the problem? What is the question? -- Jim Cone Portland, Oregon USA http://www.contextures.com/excel-sort-addin.html (30 different ways to sort in excel) "Gordon" <gwelch...

Why do series names on charts disappear when re-opening excel?
I have set up some bar charts in excel to display the series names and values of each series. However, when I close the application and re-open it, the series names are gone. I have checked the chart options, and the series name box is still checked. How do I fix this? Thanks ...

numbering pages in footer
In the old excel, 1997, we could start a new page with a different page number rather than 1. I have many pages in one worksheet and many worksheets. I want the numbering footer to continue with each worksheet I prepare, and print them out at different times. I will finish one worksheet and print, and use this while working on a new worksheet, but want the next page to start where the last worksheet ended. I know you can do this by added all the worksheets together on a single worksheet and group them, however I work them at different times. On the 1997 version, you would simple ...