Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transfering 100 named ranges to an another workbook
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transfering 100 named ranges to an another workbook
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. Oldjay wrote: 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 -- Dave Peterson |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Transfering 100 named ranges to an another 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 -- Gary''s Student - gsnu200736 "Oldjay" wrote: 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
macro to delete all named ranges in a workbook en masse? | Excel Discussion (Misc queries) | |||
Concatenate a reference to named ranges in other workbook | Excel Worksheet Functions | |||
Phantom named ranges in a workbook? | Excel Discussion (Misc queries) | |||
Linking to named ranges in another workbook | Excel Discussion (Misc queries) | |||
Poor Workbook Performance due to Named Ranges | Excel Discussion (Misc queries) |