Copy the underlying names data in desired location

Download the Workbook

Create Names workbook level and sheet level

Private Sub CommandButton1_Click()
'List Names
Sheets("Sheet2").Range("I2").ListNames
End Sub
Private Sub CommandButton2_Click()
'Workbook level names
Dim i As Integer
For i = 2 To Range("B2").End(xlToRight).Column
Range(Cells(2, i), Cells(6, i)).Select
ThisWorkbook.Names.Add Name:=Cells(2, i).Value, RefersTo:=Selection
Next
End Sub
Private Sub CommandButton3_Click()
'Sheet Level Names
Sheets("sheet2").Names.Add Name:="Sh_Jan", RefersTo:=Range("B9:B13")
Sheets("sheet2").Names.Add Name:="Sh_Feb", RefersTo:=Range("C9:C13")
Sheets("sheet2").Names.Add Name:="Sh_App", RefersTo:=Range("D9:D13")
Sheets("sheet2").Names.Add Name:="Sh_Ban", RefersTo:=Range("E9:E13")
Sheets("sheet2").Names.Add Name:="Sh_tricks", RefersTo:=Range("F9:F13")
End Sub

Name Manager - Creation of Names

Create Column Headers as Name

Private Sub CommandButton1_Click()
'Create column headers as named ranges
Dim colcnt As Integer
colcont = Range(Range("A1"), Range("A1").End(xlToRight)).Columns.Count
For i = 1 To colcont
'Range(Cells(1, i), Cells(1, i).End(xlDown)).Rows.Offset(1, 0).Select
Range(Cells(1, i), Cells(1, i).End(xlDown)).Rows.Select
Selection.Name = Cells(1, i)
Next
End Sub

Naming Procedure

(i) Names can defied at WORKBOOK LEVEL\Global:

(ii) Names can defied at SHEET SPECIFIC\lOCAL: It enables the user to use in any sheet with in workbook

Defining NAMES to Ranges

Private Sub CommandButton1_Click()
Range("A1:A5").Name = "First"
Range("B1:B5").Name = "second"
Range("C1:C6").Name = "Third"
Range("D1:D6").Name = "Forth"
End Sub

Define name at workbook level:

Private Sub CommandButton1_Click()
Names.Add Name:="Total", RefersTo:="=Sheet3!$A$1:$A$6"
End Sub

Define name at Sheet level:

Private Sub CommandButton1_Click()
Names.Add Name:="sheet4!limited", RefersTo:="=Sheet3!$A$1:$A$6"
End Sub

Assign variable to Name

Created ABC as name and assigned the string as HOW ARE YOU

Private Sub CommandButton1_Click()
Dim j As String
j = Range("A1").Value
'j="how are you"
Names.Add Name:="abc", RefersTo:=j
End Sub

Refer Names - with workbooks

Range("[Abc.xls]Sheet1!myranges").BorderAround Weight:=xlThin
'Goto Method redirects workbook and Range
Application.Goto reference:="Abc.xls!MyRanges"
Selection.BorderAround Weight:=xlThin
'Select in active workbook
Range(myranges).Select
Selection.BorderAround Weight:=xlThin

CreateNames - Top,Left,Right,Bottom