Copying Range Names to another open workbook

[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

 

[Guest]

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

 

[ll]

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

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

 

[ll]

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

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

Thanks again,
Louis

 

[Guest]

Yes, LOldWb.Activate would work.