Naming ranges as a copy of another sheet

Dear all,

    There are two sheets X and Y in my workbook. On A, there are hundreds of 
ranges named locally (i.e. names are like "X!students"). How to write a 
macro to name the respective areas in Y with the same local name? For 
example, if X!$A$1:$B$4 is named as "X!students", then I want Y!$A$1:$B$4 to 
be named as "Y!students".

    Thanks in advance.

Best Regards,
Andy 


0
1/17/2006 8:16:44 PM
excel 39879 articles. 2 followers. Follow

1 Replies
429 Views

Similar Articles

[PageSpeed] 55

Something like:

Option Explicit
Sub testme01()

    Dim wksMstr As Worksheet
    Dim wksOther As Worksheet
    Dim nm As Name
    Dim testRng As Range
    Dim ExclamPos As Long
        
    Set wksMstr = Worksheets("x z")
    Set wksOther = Worksheets("y z")
    
    For Each nm In wksMstr.Names
        Set testRng = Nothing
        On Error Resume Next
        Set testRng = nm.RefersToRange
        On Error GoTo 0
        
        If testRng Is Nothing Then
            'do nothing
        Else
            ExclamPos = InStr(1, nm.Name, "!", vbTextCompare)
            If ExclamPos > 0 Then
                With wksOther
                    .Names.Add _
                       Name:="'" & .Name & "'" & Mid(nm.Name, ExclamPos),
_                     
                       RefersTo:=.Range(testRng.Address)
                End With
            End If
        End If
    Next nm
End Sub


I'd 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




Andy Chan wrote:
> 
> Dear all,
> 
>     There are two sheets X and Y in my workbook. On A, there are hundreds of
> ranges named locally (i.e. names are like "X!students"). How to write a
> macro to name the respective areas in Y with the same local name? For
> example, if X!$A$1:$B$4 is named as "X!students", then I want Y!$A$1:$B$4 to
> be named as "Y!students".
> 
>     Thanks in advance.
> 
> Best Regards,
> Andy

-- 

Dave Peterson
0
petersod (12005)
1/17/2006 8:47:40 PM
Reply:

Similar Artilces:

Outlook not receiving emails until another is sent or opened?
I have users on Microsoft Outlook XP and 2003 (with Exchange 5.5) who do not receive emails when they are sent. They will receive them only when they either send an email or open another previously received email. We have had this issue a few times and are unable to come up with a solution. On Wed, 12 Jan 2005 11:07:04 -0800, Ed Panzeter wrote: > I have users on Microsoft Outlook XP and 2003 (with Exchange 5.5) who do not > receive emails when they are sent. They will receive them only when they > either send an email or open another previously received email. We have had ...

Copy of attachment created
I've been using outlook for years but about a month ago, whenever I send an email with an attachment, a copy is made and it sits in my outbox. It is an email that is not addressed but has the same attachment in it. Any suggestions on why this is happening and how to stop creating these extra messages? are you using any add-ins? what about rules on sent items? What type of email account? What firewall, antivirus and antispam products are installed? -- Diane Poremsky [MVP - Outlook] Author, Teach Yourself Outlook 2003 in 24 Hours Need Help with Common Tasks? http://www.outlook-t...

How do I specify column for named range of rows
I have a sheet on which new lines of data get added almost daily, the first column being a time and date stamp. To keep things organized, I name ranges of rows, describing the time interval they contain. For example, the range $10:$500 could be named "June". Now I want to chart different time intervals in a chart only by changing the name in the SERIES function. For example, if I am plotting column B against time, I would want to specify like SERIES(,June A, June B). I know this syntax is not accpeted, but is there any way it can be done, specifying only the column and ro...

The easiest way to copy a sheet of formulas! (Excel 2007)
Hi everyone! After many hours of fiddling with Excel 2007, I believe that I have finally discovered the easiest way to copy a full sheet of formulas between workbooks which eliminates references to the source workbook. Several people here have suggested solutions which involve the use VBA code or copying each individual formula from the formula bar and pasting them into the destination sheet's cells. I believe that I found an easier way (with Excel 2007) Suppose you have two workbooks: "formulas.xlsx" and "dest.xlsx". 1) Go to "formulas.xlsx"...

want to see excel author name tab
I used to see an author name tab when I would save a file using save as, but now I no longer even see the tab. How can I turn that function (2007) back on? Hi, I don't understand what you mean by '> I used to see an author name tab when I would save a file using save as' It has always been available under File|Properties but not as far as I know under the file|Save as dialog. To see it in E2007 Office Button|Prepare - Properties -- Mike When competing hypotheses are otherwise equal, adopt the hypothesis that introduces the fewest assumptions while ...

Highlight the cell/row if particular name comes..
Hi, This is what I would like to achieve, There will be sheet full of address details and in that I want to highlight the cells with some colour if the particular list(i.e.list of 10 particular area names like Area1,Area2....Area10) of area names present along with the door # and Street name. The challenge is that the area names will be mixed with street name ect., in the same cell. I can guess that this is possible with macro but my knowledge in that is ZERO.. Pls help on this... Can you give us some examples of what your data looks like, and details of what columns you...

Change Display Names in Exch '03 to Last,First
In Exchange 2000, you could use ADSI and scripts to change display names for contacts/users from firstname,lastname to lastname,firstname. However, I am working with a new client that has a 2003 Exchange server up and populated, but the GAL shows in firstname,lastname against his will. How can I change this so that the GAL is listed the way he wants without individually modifying each contact/user? -- Lydell Anderson A+, Net+, MCP, MCSA I don't see how it should be any different with Exchange 2003. Modifying the Display name will change how the GAL is displayed. You should be ...

Find a Item & Copy the Data
Can someone please help me? I've got a huge SAP generated Parts List from overseas which I need to find a unique number in column A. (Sheet1) There are 10 columns and in excess of 55,000 rows This SAP Text number is comprised of 18 fixed digits (eg: 000000065202255411). However the number itself is variable with leading zero's (eg: 000000000063060003), and anything in between if you follow my drift. My problem is I need to verify each customer's price and purchase cost from this data sheet. I've managed to automate most of the process but I can't return the...

No "Copy Sheet" option in Visio 2003
Gurus, Is there no "Copy Sheet" option in Visio 2003 like there is in Excel? The closest I can find is "Copy Drawing". -- Spin On Sun, 24 Feb 2008 11:37:58 -0500, "Spin" <Spin@spin.com> wrote: >Gurus, > >Is there no "Copy Sheet" option in Visio 2003 like there is in Excel? The >closest I can find is "Copy Drawing". Would you please like to give http://www.visio-utilities.sandrila.co.uk/ a go where we have added the ability to a. duplicate a page within the current document b. duplicate a page to another open docume...

Fill Color a Range
I want to fill a range with a color. I know how to do that but I don't know how to keep the grid lines visible in that range. It seems I would need a transparent color or something like that. How can I fill a range with a color that will allow the grid lines to show through? Thank you. The only way is to give the gridlines an alternative colour of their own. -- HTH RP (remove nothere from the email address if mailing direct) "Jack Gillis" <XXXXXXXX@widomaker.com> wrote in message news:112bdn5bm4udrff@corp.supernews.com... > I want to fill a range with a ...

Variable Sumproduct Range
Hoping someone far smarter than myself can help. The below formula will give a result where column A=Monday, col B=John and sum the corresponding values in column C. =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(C5:C10)) But what if I need the (C5:C10) part to be variable from anywhere between columns C to G, and determined by a value entered into say cell A1. Meaning if the number 1 was typed into that cell the formula would be as above: =sumproduct(--(A5:A10="Monday"),--(B5:B10="John"),(C5:C10)) or if the value typed into A1 was...

hide names in distribution list
How can I hide names in distribution list? Using outlook 2002? Not possible with an Outlook/MAPI DL....I'd suggest putting your own name in the TO field, and put the DL/recipient names in the BCC (blind carbon copy) field. robert wessel wrote: > How can I hide names in distribution list? > Using outlook 2002? Use the BCC field. --� Milly Staples [MVP - Outlook] Post all replies to the group to keep the discussion intact. Due to the Swen virus, all e-mails sent to my actual account will be deleted w/out reading. After searching google.groups.com and finding no answer robert we...

How to change name of an XML node
Hi, Is there any way to change the name of XML node? In the following xml, I want to change the node <FIELD_1> to <FIELD_ITEM> in all the places. Ex: <NewDataSet> <LOOKUP> <FIELD_1>First_Item113</FIELD_1> <FIELD_2>First_Item224</FIELD_2> </LOOKUP> <LOOKUP> <FIELD_1>Second_Item11</FIELD_1> <FIELD_2>Second_Item222</FIELD_2> </LOOKUP> <LOOKUP> <FIELD_1>Third_Item11</FIELD_1> <FIELD_2>Third_Item22</FIELD_2> </LOOKUP> </NewDataSet&g...

Inventory Received/Booked/Commited during Date Range
Does anybody have a report that shows me the detailed cost amounts and quantity per P.O. of goods received during a certain time frame? So if I receive part of a P.O. in April and Part of a P.O. in May and I run the "received report" for April I only want to see the quantities and cost for the part of the P.O. that was received in April. Never mind, I found the report that I needed on Customer Source. "Alex" <Alex@no.com> wrote in message news:eupu52zyIHA.548@TK2MSFTNGP06.phx.gbl... > Does anybody have a report that shows me the detailed cost amounts and ...

How do you make bubble sheets?
I am trying to make bubbles sheets for a test. Does anyone know of an easy way to do this? Hi, I've seen it done in Excel but in Project?? -- Jan De Messemaeker Microsoft Project Most Valuable Professional +32 495 300 620 For availability check: http://users.online.be/prom-ade/Calendar.pdf "animallover" <animallover@discussions.microsoft.com> wrote in message news:59130AF6-6E59-4B14-8C41-CE522CE089C5@microsoft.com... >I am trying to make bubbles sheets for a test. Does anyone know of an easy > way to do this? ...

Strange character on sheet prevents correct formatting
I have a spreadsheet in 2000, that has the ` character, right in front of all the text in every cell. I know this is a non-showing character. When I try to change my date format from xx/xx/xx to xx/xx/xxxx this character prevents this from happening. If I delete this either in the cell itself or the formula bar the format will then change My problem is I have another spreadsheet that has this about 500 times and so far I have only been able to do it manually. Can anyone tell me how to remove this on the entire sheet? The only thing I haven't tried is the "clean" command...

I cannot copy email and paste into Word 7
In the past I have been able to copy from internet and paste into Word 7. Suddenly I cannot. I cannot find a setting that I might have accidently changed. The only thing I can think of is that some websites don't allow a user to use the "copy and paste" function. The only thing I can think of is to try using the keyboard command ctrl+C and to copy and ctrl+V to paste into document. "Duanne" wrote: > In the past I have been able to copy from internet and paste into Word 7. > Suddenly I cannot. I cannot find a setting that I might have accidently ...

Problem with printing multiple copies of a report
I read and follow the suggestion from this group to wrtie the code for a print sub: DoCmd.RunCommand acCmdSaveRecord DoCmd.SelectObject acReport, "rptCertificate", True DoCmd.PrintOut acPrintAll, , , , InputBox("Enter Number of Copies", _ "Print repCertificate", 1), 0 Access prints one copy even I put 2 in the InputBox or use DoCmd.PrintOut acPrintAll, , , , 2, 0 in stead. Thank you in advance for your help. -- Jeff ...

how can i open pocket excel files when copying to my laptop
i have pocket excel files which i have copied to my laptop. they won't open because i can't find a reader for the .pxl extension. has anybody solved this yet? ...

names, labels
I have 12 worksheets, one for each month of the year. Each worksheet does identical calculations, so I need the names to be worksheet defined rather than workbook defined. And indeed they seem to be, yet they still cross sheet boundaries. For example on the June sheet, Insert>Name>Define shows: Loan =June!$D$8:$D$15 but the July, August, etc. sheets all see the June definition of Loan. In other words, if on the August sheet I use =sum(Loan), it calculates the result for the June sheet. I would have thought that June! would have hidden these names from any other sheet. How do I keep...

Reply to and Reply to All populate senders name incorrectly
I have 3 users in our company that are experiencing reply and reply to all random issues, users state when they reply to an e-mail, they are getting a copy of the reply to their Inbox. Reply to all is expected, however it will sometimes add the user but not always. I have updated to Office 2K Prem with all updates and still am getting the same results. Has anyone had similar issue? Any help appreciated. Thanks, the_bry ...

Excel 2002 copy multiple rows to another worksheet
I have a database with 2000 rows of data, I want to copy and paste 25 non contiguous rows to another worksheet. I select the rows I want and when I press copy I get an error message that you connot copy multiple rows. Is there a work around method I can use so I do not have to copy each row separately. Harvey If you have a common criteria in your selection, there's probably a way to extract these lines dynamically in that other sheet. If so, you could try describing your db sheet set-up (the sheetname, what's the key col(s), in which row does data start, the expected extent...

Filter rows containing cells with values within a range (for example <11 or >10 but <21)
Column B consists of Brand names (each brand has numerous products) Column C consists of Product names (over 500 products) Coumns D to CG (row 1) consist of months going back a few years. The data from D2:CG539 consists of the rank of each brand for each month. I would like to filter my rows so that I can view only the products that have ever ranked in the top 10. I already have a couple of formulas that allow me to filter my data. Example 1: If I want to know the highest rank ever achieved by a product I use this formula: =MIN(D2:CG2) and then drag it down. Now I can use autofilter and see w...

update yes / no field in table based on another table
I am wanting to update a yes/no field in one table based on another table. If the name is in both tables to put a "yes" value in the field If the name is in one table but not the other to put a "no" value in the field For example: Table 1: Name Table 2: Name Check box = yes Table 1: Null (Name not in table) Table 2: Name check box = no On Mon, 3 Mar 2008 14:55:01 -0800, Bryan <Bryan@discussions.microsoft.com> wrote: >I am wanting to update a yes/no field in one table based on another table. > >If the name is in both tables to put a "yes" va...

Understanding DNS :Domain Naming Server
Here is a good explanation of DNS, how it works, why it is used.. you can read more at http://www2.isupportyou.net/2010/07/understanding-dns-domain-naming-server.html ...