Copying Range Names to another open workbook

  • Thread starter Thread starter ll
  • Start date Start date
L

ll

Hi,
I am trying to copy my range names to another open workbook. I've
been looking at the script below but can't figure out how to get it to
copy the range names from the first (LOldWb) workbook to the second
(LNewWb) workbook. Thanks, Louis
-------------------------

'Define old and new workbooks
Workbooks.Open Filename:=UserForm1.TextBox1.Value
LOldWb = ActiveWorkbook.Name

Workbooks.Add
LNewWb = ActiveWorkbook.Name


'Loop to copy range names from old to new workbooks

For Each x In LOldWb.Names

Workbooks(LNewWb).Names.Add Name:=x.Name, _
RefersTo:=x.Value
Next x
 
II,

give this a try:

'Define old and new workbooks
Dim LOldWb As Workbook
Dim LNewWb As Workbook
Dim x As Name

Set LOldWb = Workbooks.Open(Filename:=UserForm1.TextBox1.Value)
Set LNewWb = Workbooks.Add

'Loop to copy range names from old to new workbooks
For Each x In LOldWb.Names
LNewWb.Names.Add Name:=x.Name, _
RefersTo:=x.RefersTo
Next x
 
II,

give this a try:

'Define old and new workbooks
Dim LOldWb As Workbook
Dim LNewWb As Workbook
Dim x As Name

Set LOldWb = Workbooks.Open(Filename:=UserForm1.TextBox1.Value)
Set LNewWb = Workbooks.Add

'Loop to copy range names from old to new workbooks
For Each x In LOldWb.Names
LNewWb.Names.Add Name:=x.Name, _
RefersTo:=x.RefersTo
Next x
Click to expand...


Thanks for your help in this - however, it is throwing an error.

Elsewhere in my code, I am getting the 'type mismatch' error on the
following line:
Windows(LOldWb).Activate

Does this need to be simply LOldWb.Activate?

Thanks
 
Thanks for your help in this - however, it is throwing an error.

Elsewhere in my code, I am getting the 'type mismatch' error on the
following line:
Windows(LOldWb).Activate

Does this need to be simply LOldWb.Activate?

Thanks
Click to expand...


I just have gotten this to work. I did simply replace all instances
of Windows(LOldWb) to LOldWb

Thanks again,
Louis
 
Back
Top