Excel Guides

Copying Named Ranges in Excel

There are a few different ways to copy named ranges in Excel. One way is to use the Name Manager. To do this, go to the Formulas tab and click on Name Manager. Then, select the named range you want to copy and click on the Copy button. You will then be able to paste the named range into another worksheet.

Another way to copy a named range is to use the Create From Selection feature. To do this, first select the cells that you want to name. Then, go to the Formulas tab and click on Create From Selection. In the Create Names dialog box, check only Left Column and click OK. This will create a named range for each row of data in your selection.

You can also use VBA to copy named ranges. To do this, open the Visual Basic Editor (VBE) by pressing Alt+F11. Then, insert a new module and paste the following code:

Sub CopyNamedRange()

  Dim ws1 As Worksheet
  Dim ws2 As Worksheet
  Dim nr1 As Name
  Dim nr2 As Name

  Set ws1 = ThisWorkbook.Sheets("Sheet1")
  Set ws2 = ThisWorkbook.Sheets("Sheet2")

  For Each nr1 In ws1.Names

     Set nr2 = ws2.Names.Add(Name:=nr1.Name, _
                            RefersToR1C1:=nr1.RefersToR1C1)

  Next nr1
End Sub

This code will loop through all of the named ranges in Sheet1 and create copies of them in Sheet2.

Move beyond 

Excel

Get started with Causal today.
Build models effortlessly, connect them directly to your data, and share them with interactive dashboards and beautiful visuals.