Transfering 100 named ranges to an another workbook

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

It has been suggested to copy each one separately. There must be a better way.

The reason I an doing this is to delete some Menu Items created in Excel97
off of this large workbook. I have tried to use the Add-in that is supposed
to do this but it tells me this "this is not a Excel file". I therefore
created a new workbook and copied all the sheets, formats, comments,
validation and VBA code to the new workbook. All that remains is to import
all of the named ranges.

Can anybody help so that I do not have to create 100 new ranges

oldjay
 
You don't really want to transfer the ranges--you want to create the equivalent
names in the new workbook???

Get Jan Karel Pieterse's (with Charles Williams and Matthew Henson) Name
Manager to make checking those names easier.

You can find it at:
NameManager.Zip from http://www.oaltd.co.uk/mvp

You can pick up all the names and put them into a sheet (with their
definitions).

Then you can move that worksheet to the other workbook, clean up that list, and
then apply those names to the new workbook.
 
The following will create Named Ranges in destination.xls to match the ones
in source.xls:

Sub namexfr()
wbs = "source.xls"
wbd = "destination.xls"
For Each nam In Workbooks(wbs).Names
Workbooks(wbd).Names.Add Name:=nam.Name, RefersToR1C1:=nam.RefersToR1C1
Next
End Sub
 
Back
Top