How To Copy Name Ranges in Excel 2003 to Another Workbook

Macros are small applications that run a specific task. In Microsoft Excel, there is a macro that will copy all the range names from one workbook and paste them into another. Creating this macro will pull the range names and paste them automatically.

Skill level:Easy

Instructions

1 Open the workbook you will be copying the range names from. If you do not already have the names set, define the range names. To do so, click on the "Insert" menu, select "Name" and then choose "Define" from the list. Find the names in the workbook box.

2 Locate the name of the second workbook. Make sure that workbook is saved as a Macro-enabled Workbook. The file extension should be .xlsm or .lsm.

3 Hold down the "Alt" and "F11" keys. The Visual Basic Editor will open. Click "Module" in the "Insert" menu. The module window will pop-up.

4 Type the following code into the window. Change the reference to book2.lsm or book2.xls to the name of the book you are pasting too. Sub Copy_All_Defined_Names() ' Loop through all of the defined names in the active ' workbook. For Each x In ActiveWorkbook.Names ' Add each defined name from the active workbook to ' the target workbook ("Book2.xls" or "Book2.xlsm"). ' "x.value" refers to the cell references the ' defined name points to. Workbooks("Book2.xls").Names.Add Name:=x.Name, _ RefersTo:=x.Value Next x End Sub

5 Close the Visual Basic Editor Window. Click on the "X" button or hit "ATL" and "F11" again to close the window. The macro will automatically save. When you are back at book 1 or the workbook you are copying from, save the workbook. Click on "File" and choose "Save" or "Save as." Do not change the name of the file in any way.

6 Open the workbook you are pasting to or start a new workbook. Save the workbook as an Excel Macro-Enabled Workbook. Make sure the name is the same as the name for book2.lsm in the code. The spelling capitalisation and extension have to be exact.

7 Open the first workbook again and click on the "tools" menu. Select "Macro" and click "Macros."

8 Click on "Copy_All_Defined_Names" from the list of macros. Select the "Run" option. The names will be automatically pasted in the second workbook.

9 Open Book 2 or the paste destination workbook. All the names should be pasted.