How to Move/Copy Named Ranges of one workbook to another with VBA

[jamilm]

Gurus,

is it possible to insert the specific named ranges of one workbook to another workbook using VBA?


for example

i have workbook named ("Book1") in which my named ranges are and i want to copy named range DynRngAcc and DynRngProd to another workbook ("Book2").

any help is appreciated.

thanks.

 

[wrightyrx7]

Hi there,

This code will go in a module of the Workbook WITH the Named Ranges.

Open the Workbook that you want the ranges copying to...

Change "Book2.xlsx" to the NAME of the workbook you want the ranges copying to.

Sub Copy_All_Defined_Names()
     For Each x In ActiveWorkbook.Names
      Workbooks("Book2.xlsx").Names.Add Name:=x.Name, RefersTo:=x.Value
   Next x
End Sub

 

[jamilm]

Thank you very much wrightyrx7.

Hi there,

This code will go in a module of the Workbook WITH the Named Ranges.

Open the Workbook that you want the ranges copying to...

Change "Book2.xlsx" to the NAME of the workbook you want the ranges copying to.

Sub Copy_All_Defined_Names()
     For Each x In ActiveWorkbook.Names
      Workbooks("Book2.xlsx").Names.Add Name:=x.Name, RefersTo:=x.Value
   Next x
End Sub

 

[wrightyrx7]

No problem

 

[bk4u]

Wrightyrx7,

Appreciate the code to move named ranges from one workbook to another with VBA. It was just what I needed!
Is there a way a macro could rename part of a cell named range in a workbook? I would like to change the same occurrence of a string in each cell name with another character string using VBA instead of having to edit each cell name using the Name Manager.
Thanks for any help you may be able to provide!

BK4U

 

[helpexcel]

Is it possible to alter this code so that it goes into the workbook you are copying to? I have code that allows you to select an open workbook to copy, and i like to use this code to not only bring over the data, but also the named ranges used in the formulas.

 

[bk4u]

helpexcel,

You would have to ask Wrightyrx7 for assistance with your question. He wrote the original VBA code. I do not know how to alter his code for use in the workbook receiving the data, as you are currently doing with your code for data.
His code for named ranges works perfectly, but his code has to be in the workbook with the named ranges in order for the named ranges to be copied to another workbook. The only modification to his code is changing the "Book2.xlsx" portion of the code to the name of the workbook receiving the named ranges.
Hopefully he or someone else seeing this will be able to assist you.

BK4U

 

[GWteB]

@helpexcel, this might give you an idea ...

Sub UsageExample()

    Dim sourceWb    As Workbook
    Dim destWb      As Workbook
    
    Set sourceWb = Application.Workbooks("WorkbookInWhichTheNamesAre")
    Set destWb = Application.Workbooks("ReceivingWorkbook")
    CopyNames sourceWb, destWb

End Sub

Public Sub CopyNames(argSourceWorkbook As Workbook, argDestinationWorkbook As Workbook)
    Dim Nm  As Name
    For Each Nm In argSourceWorkbook.Names
        argDestinationWorkbook.Names.Add Name:=Nm.Name, RefersTo:=Nm.Value
    Next Nm
End Sub

 

[helpexcel]

thanks. i adjusted the code as noted below and i'm getting a run time 1004 error - application defined or object defined error.

Set sourceWb = Workbooks(listchoice1) 'Application.Workbooks("     WorkbookInWhichTheNamesAre")
    Set destWb = ThisWorkbook 'Application.Workbooks("ReceivingWorkbook")

 

[GWteB]

The first line of your code assumes four conditions, and all of them should be true:
- there's a String type variable named listchoise1;
- that variable is within scope of the current code procedure;
- that string containes the name of an existing workbook;
- that workbook has already been opened in advance.
The second line of your code looks okay.