Naming Ranges #4

Hi,

When I name a Range on Sheet1, then make a Copy of Sheet1 to say Sheet2, 
then access the Define Names dialogbox, I can see the named Range listed 
twice. One is noted as belonging specifically to Sheet2 and the other appears 
without a sheet reference.

When assiging a name to a Range, can I specify that it belongs to a certain 
sheet?

Also, how would I change this code to assign the sheet name aswell?

ActiveWorkbook.Names.Add Name:="myRange", RefersToR1C1:="=Sheet1!R1C1:R10C1"

I am trying to assign a the name 'Column_Header_Range' to each sheet in my 
workbook and I want to do it through a macro

Thanks

Simon Shaw



thanks

Simon

0
SimonShaw (26)
2/14/2005 8:09:04 PM
excel.misc 78881 articles. 5 followers. Follow

1 Replies
525 Views

Similar Articles

[PageSpeed] 33

If you include the sheet name in the name, then that name is local to that
sheet.

Manually, it would be like entering this:

sheet1!myName
in the "Names in workbook" box.

In code:

ActiveWorkbook.Names.Add Name:="Sheet1!myRange", _
    RefersToR1C1:="=Sheet1!R1C1:R10C1"

Or maybe even easier to see:

Activesheet.names.add Name:="myRange", RefersToR1C1:="=R1C1:R10C1"

I kind of like this style:

    With ActiveSheet
        .Range("A1:A10").Name = "'" & .Name & "'!myRange"
    End With

The single quotes aren't always required--but they don't harm anything.

And if you're working with names, do yourself a giant favor and get 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

Simon Shaw wrote:
> 
> Hi,
> 
> When I name a Range on Sheet1, then make a Copy of Sheet1 to say Sheet2,
> then access the Define Names dialogbox, I can see the named Range listed
> twice. One is noted as belonging specifically to Sheet2 and the other appears
> without a sheet reference.
> 
> When assiging a name to a Range, can I specify that it belongs to a certain
> sheet?
> 
> Also, how would I change this code to assign the sheet name aswell?
> 
> ActiveWorkbook.Names.Add Name:="myRange", RefersToR1C1:="=Sheet1!R1C1:R10C1"
> 
> I am trying to assign a the name 'Column_Header_Range' to each sheet in my
> workbook and I want to do it through a macro
> 
> Thanks
> 
> Simon Shaw
> 
> thanks
> 
> Simon

-- 

Dave Peterson
0
ec357201 (5290)
2/15/2005 1:17:51 AM
Reply:

Similar Artilces:

Delivery Status Notification #4
One of our subsidiaries is getting frequent but inconsistant delivery errors when sending e-mail to us. To our knowledge, they are the only ones that have this issue. According to them, they only have this problem with us. We use Exchange Server 2003, SP2 - all current exchange and windows patches. They use a linux email program, sorry I do not have specifics on it. The notification they get is as follows: - These recipients of your message have been processed by the mail server: userid@domain.com; Failed; 5.3.0 (other or undefined mail system status) Remote MTA smtp-relay.pbi.net:...

Need to put a name in the column heading
I am a new user and I cannot seem to put titles in the column headings. Thanks for your help. The help section was not of any use for the searches I made. If you mean that you want to replace the "A", "B", etc., the borders are not customizable. -- Jim Rech Excel MVP "windsorhartung" <windsorhartung@discussions.microsoft.com> wrote in message news:7C556A73-3230-4FB8-A9AA-EAA36E5B15C3@microsoft.com... |I am a new user and I cannot seem to put titles in the column headings. | Thanks for your help. The help section was not of any use for the searches ...

IE Temp Names and Excel
I use a web-based application that generates CSV files. When I open these from Internet Explorer they are automatically loaded into Excel (2003). However after a few minutes I always get an auto save error. The reason is that the filename created by IE will include something such as [1].csv at the end of the filename. Excel does not consider these valid characters and so gives an error when it attempts to autosave (but oddly, obviously can open files with such a name OK). It seems odd to me that IE creates a filename that Excel says is invalid. Is there a solution to this (other than disablin...

Using variable in field name
Hi. I have a table whose fields are named boolType00, boolType01, boolType02, ..., boolType19. I need to make a loop and real all of the 20 fields. Something like: Dim i1 As Integer i1=0 While i1 < 20 Debug.Print MyTable!boolType"i1" i1=i1+1 Wend Is there a way to do it? Thanks H. Martins If you OpenRecordset, you can refer to the field in the recordset like this: Debug.Print rs.Fields("boolType" & i1) If you need help with using a recordset, see: http://allenbrowne.com/func-DAO.html#DAORecordsetExample Lots of repeating fields like that usua...

UPC Equation #4
Yeah the first half was dead on Niek, Thanks! The "C" equation did not work however. I definietly got an answer for multiple fields. Now i just need to figure out a way (if possible) to conjunct that together so that when i enter an 11 digit number in A1 the corresponding 12 digit number in B1 appears. I would allow for more fields but her employer will be looking at this and is none to bright. If we have a middle step on the exel sheet, it is likely to cause more problems than solve. By the way, thanks for all the help so far, it has really been appreciated. Solutions? -- P...

dynamic defined name
i have 12 defined names correspond to year monthes (represented in 12 folders) in each folder i have workbook with the same name and structure but sure different data; i want in new workbook (same structure) to sum same cells from different monthes (different difined names) due to user choise; for example : if user entered from 5 to 9; this means that i need to sum cells from defined names for folders (May, June, July, August, September) Please dont help with code, only excel functions Do you really mean folders (directories) or are you really referring to other worksheets or ...

name conflict #2
"name cannot resemble a reference" window comes after trying to change settings from r1c1 refeence style to a1.b1 style. any help will be appreciated. thanks a lot Giulio, this prompt appears because a name that resembles a cell reference ("A1" for example) has been assigned either to a single cell or to a range somewhere in the workbook. The prompt should indicate the name that Excel has detected. If it does not, cancel the prompt, then check the names box by selecting Insert > Name > Define. Change or delete any names that resemble cell references, and you sho...

Resolving Names in Outlook 2000
Issue: Name will not resolve when addressing using "firstname lastname" format. Name will resolve when using "lastname, firstname" format. In AD, the user has the following setup: First Name: Jonathan Last Name: Smith Display Name: Smith, John Description: John Smith Incidentally, I too have a similar setup in which I my firstname in the "First Name" field and the "Display Name" field do not match but there are no issues with resolving my name when using the "firstname lastname" format. Can you help us identify what fields Exch...

How to update Ft End Queries when changing Bk End Table Name?
I changed the name of a table on the Back End of the data base. I thought I could just repoint the queries in the Front End to the new table name by changing the query properties. Nope. Didn't work. How can I get the Front End to keep up with changes I make on the backend like changing a table name? thanks for any help. MyEmailList@gmail.com wrote: > I changed the name of a table on the Back End of the data base. > > I thought I could just repoint the queries in the Front End to the new > table name by changing the query properties. > > Nope. Didn't work. > &...

Dynamic Named Ranges #2
I am trying to build a report in excel that auto updates a query from an access database and I would like to use named ranges to make the formula writing easier. The problem is I dont know exactly how many rows are going to be coming in from the query each time as there is potential for the dataset to grow. Now i have the ranges defined as $A:$A but I am wondering if there is a better way to do this.... Thanks in advnace for your help Chuck -- clane ------------------------------------------------------------------------ clane's Profile: http://www.excelforum.com/member.php?action...

can't delete messages #4
When I try to delete a message in the inbox I get a message 'The meswsaging interface returned an unknown message. If problem persists restart outlook'. Restarting dooes not solve the problem. I also reinstalled outlook over the existing program without success. Any advise? Peter Z <anonymous@discussions.microsoft.com> wrote: > When I try to delete a message in the inbox I get a > message 'The meswsaging interface returned an unknown > message. If problem persists restart outlook'. Restarting > dooes not solve the problem. I also reinstalled outlook >...

CRM 4.0 Installation error #3
Hi, i am trying to install Dynamics CRM4.0, but I get an error in the setup check. The error is: Service msftesql$CRM was not found on computer 'CRMSERVER'. The specified service does not exist as an installed service I checked in the help what can I do and it say that the possible reasons for this error are: 1. SQL Server FullText Search service is not running 2. You do not have sufficient rights I check the SQL Server FullText Search service and it is up and running. I am installing the CRM server as Administrator and I do not believe that i do not have rights or permissions....

excel file deleted. Now howdo you delete file NAME
I deleted an excel file I had typed. Now I can't figure out how to delete the file name. It still shows up when you click on open to see the files. You can still open this but there is nothing there. Need to delete FILE NAME you can configure to show 0 files or open n files until disapear the deleted file. "NANMAC" wrote: > I deleted an excel file I had typed. Now I can't figure out how to delete > the file name. It still shows up when you click on open to see the files. > You can still open this but there is nothing there. Need to delete FILE NAME ...

Copying vertical and horizontal ranges of cells
Will excel allow me to copy a range of vertical cells on one workboo page to a horizontal range on another page? If so, how? Thanks -- Message posted from http://www.ExcelForum.com Hi Checkman............, yes, look up TRANSPOSE in HELP, when you paste there is a transpose option on the dialog. --- HTH, David McRitchie, Microsoft MVP - Excel [site changed Nov. 2001] My Excel Pages: http://www.mvps.org/dmcritchie/excel/excel.htm Search Page: http://www.mvps.org/dmcritchie/excel/search.htm "Checkman >" <<Checkman.17uzi0@excelforum-nospam.com> wrote i...

find maximum value in range of fields
Hello Could someone advise/assist with the following: I have fields like the following: Badge Date 1 Badge Date 2 Badge Date 3 etc... to Badge Date 7 What I would like to have happen is when a new badge date is entered the badge dates would cascade -- dropping the oldest date and cascading badge dates -- ( i.e. Badge Date 1 would be the newly entered date - Badge Date 2 would be the date previously in Badge Date 1 - previously in Date 2 goes to Date 3, etc...) OR The ability to pull the maximum ( most current date) from the Badge Date fields. Basically I want to keep the last 7 dates fo...

excel case changes on a column of first names
Could somebody please tell me how to work the "proper" formula so that it will change a column of names from all caps to just first letter cap? I can get the formula to work for the first cell, but I can't get it to copy or paste, or paste special to the column below it. (I don't use excel very often and therefore have limited knowledge of these functions.) Thank you! jim_in_mich Jim, One easy way is to insert a new column next to the column that has the non-proper (upper or lower) case words or names. Presuming that you'd have the names such as JOHN DOE in A1 a...

Name Conflict window pops up, when I try to open a saved worksheet
I keep getting this window and no matter how i try to rename it , it wont let me, but it wont open -- w ...

Chart with 4 different axis ?
Hello, for a particular analysis I would like to use all 4 axis with different label (scales). Is that feasable, and if so, how can I tackle the task? Thank you in advance! Kind regards, H.G. Lamy H.G. Lamy - Create a chart with two or more data series. Select one data series and use Format Axis to change it to secondary axis. If necessary, use Chart Options to display all axes. Format each axis, paying attention to Patterns tick mark and label locations and to Scale max, min, and crosses at. For more specific instructions, specify your version of Excel. An ...

? CListCtrl::GetColumn To Get Column Name
Hi, I've written an app that's kind of like WinSpy/Spy++ to make adjustments to list controls. It can correctly set an app's list control's modes, alignments, sorting, styles, and extended styles. It can also get the widths of the columns and count of items. The problem is that I cannot seem to get the columns' names. My app uses code like this, where lc is a pointer to the target app's list control: . lc->GetColumnWidth(.); lc->GetItemCount(.); lc->ModifyStyle(.); lc->SetExtendedStyle(.); lc->GetHeaderCtrl()->GetItemCount(); . That a...

Disable crm 4 workflow loop detect
Is there a way to disable or change the crm 4 automatic loop detect? We have a system that during the go live load we will run a workflow on 1000 objects and this workflow will call itself once then stop. While testing we are receiving the "This workflow job was canceled because the workflow that started it included an infinite loop" error message. From reading this is triggered automaticaly if a workflow calls itself more then 6 times in one hr. Can this be disabled or changed??? thx, ...

syntax to concatenate two named ranges?
I need to graph two separate named ranges as a continuous data series. Example: Series 1; "PastMonthActuals" = Sheet2("A1:A3") Series 2; "Projections" = Sheet3("A4:A12") I'm not trying to graph these as separate series, I want it to all be one line. In my real workbook these are dynamic ranges, but this simple example will suffice. What is the best way (syntax) to set up a new named range that concatenates the values so that I can feed it into my graph as one series of 12 points? I've tried the obvious, such as: "AllPts&qu...

Error when adding user to CRM 4.0
We are running an evaluation copy of Microsoft CRM 4.0. I am trying to test out the multi-homed functionality but have been unable to add a user from another domain. I have a two-way Windows trust between the two domains and it is functioning properly. When I go to add the user from the other domain it finds the user and fills in the users details but when I go to save it throughs up a generic error: "An error has occurred. For more information, contact your system administrator." Any ideas? Can you enable DEV errors and post the error message? Darren Liu, CRM MVP Crowe ...

Keypress to go to "From" name in sorted mailbox
In Outlook 2003 once I'd sorted a mailbox by From: I could hit the key for the first letter of the name and go directly to the messages from that set of names in the alphabet. This doesn't work for me in Outlook 2007 and I can't find any discussion of this anywhere. Is there a way to turn this on? Or some key combo that will do this? Thanks! Alan it still works here - make sure a name is selected in the message list so the list is in focus. -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-tips...

Same name for Windows Server and DNS
Hello everyone! I have a question... Is there any problem to give the DNS the same name as the Computer name of the Server? (I have a Small Business Server 2003 installed) Thanks a lot! ...

How to enter output range for Descriptive statistice
Hi I'm trying to get Descriptive statistics summary, and couldn't enter output range. what is the formate? Hi Tahir, I presume you are talking about the analysis toolpak's Descriptive Statistics. If so, under Output options click Output range and then type or click on the top left corner cell where you want the descriptive statistics to appear. -- Cheers, Shane Devenshire Microsoft Excel MVP "Tahir Hashmi" wrote: > Hi I'm trying to get Descriptive statistics summary, and couldn't enter > output range. > > what is the formate? ...