• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Name Manager list copying.

Eloise T

Eloise T

Active Member
Is there an easy way to copy a list of Name Managers from one workbook to another? ...and from one worksheet to another worksheet in the same workbook?
 
Last edited:
Thank you. Looks complicated, like a long recipe for baking a cake from scratch, but as long as it works....

Thank you!
 
Eloise, I apologize for not getting to this sooner. Long holiday weekend kept me away from my laptop.

I,too have tried this code and the code supplied by Allen Wyatt at https://excelribbon.tips.net/T008811_Copying_Named_Ranges.html and cannot get it to work with your workbook. However, when I set up a dummy workbook with range names and try to run the macro to copy them to a new file, it works without error. (Allen Wyatt's Macro is what I tested.) I suspect that there may be an issue with your range names. Look at Allen's comments and see if any of these apply to what you are attempting to do. Make sure that the file you are copying to has been saved before you attempt to run the macro. I found that it failed if I had not saved the target file before hand. Both files need to be open for the macro to work.
 
Hi ,

Try this :
Code: 
Sub Copy_All_Defined_Names()
    Dim x As Name
    Dim i As Integer, numberofnames As Integer
  
    numberofnames = ThisWorkbook.Names.Count
    For i = 2 To numberofnames
        Workbooks("Book2.xlsx").Names.Add Name:=ThisWorkbook.Names(i).Name, RefersTo:=ThisWorkbook.Names(i).Value
    Next i
End Sub
You need to save the target workbook before you run the macro only if you include the file extension in the code ; if you open a new workbook , which has a name Book2 , and if you have not yet saved it , then the following code will work :
Code: 
Sub Copy_All_Defined_Names()
    Dim x As Name
    Dim i As Integer, numberofnames As Integer
  
    numberofnames = ThisWorkbook.Names.Count
    For i = 2 To numberofnames
        Workbooks("Book2").Names.Add Name:=ThisWorkbook.Names(i).Name, RefersTo:=ThisWorkbook.Names(i).Value
    Next i
End Sub
Narayan
 
I tried your macro above. As long as I specify the name of an existing Excel file in the macro, AND the target Excel file is open along with the source file, it works great...really that is the only way I would need to use it.

I've been unable to get it to work using the "Book2" code unless Book2 exists and is open.

Too bad there isn't a similar solution for copying Conditional Formats. :)
 
NARAYANK991 said:
Hi ,

As far as copying conditional formats is concerned , does this not work the way it is suggested here :

https://stackoverflow.com/questions/38918475/copy-conditional-formatting-between-workbooks

Narayan
Click to expand...
I tried that and it copies too much. i.e. If the source has data with highlighting all that highlighting gets transferred as well as the process sees the highlighting as part of the Conditional Formatting when it is not. It works great if there's no data, so I have started creating a "dummy tab" solely for use with Conditional Formatting as a source to copy from.
 
You must log in or register to reply here.
Back
Top