Copy Range Names To Other Worksheets In Workbook

**The Skipper** · 01-25-2013, 04:12 PM

I have a workbook containing 12 worksheets that are identical except for the Worksheet Name. Each represents a month of the year. I would like to copy all the Named Ranges from Worksheet 1 to the other worksheets. I would appreciate your suggestions.

Thanks for your help.

**Bishonen** · 01-25-2013, 06:06 PM

Sub Copy_All_Defined_Names()

Dim worksheet As worksheet

Dim wsNamelen As Integer
Dim wsName As String
   
    For Each Name In ActiveWorkbook.Names
        For Each worksheet In ThisWorkbook.Worksheets
                If worksheet.Name <> wsName Then
                    worksheet.Names.Add Name:=Name.Name, RefersToLocal:=Name.RefersTo
                End If
        Next worksheet
     Next Name
End Sub

assuming that the scope will be set to local ofc.