Public Function copy_named_range(copy_range As Range, dest_range As Range)
Dim ws_copy, ws_dest, wb_copy, wb_dest, nm, offset_row, offset_col
Set ws_copy = copy_range.Parent
Set ws_dest = dest_range.Parent
Set wb_copy = ws_copy.Parent
Set wb_dest = ws_dest.Parent
offset_row = dest_range.Cells(1).Row - copy_range.Cells(1).Row
offset_col = dest_range.Cells(1).Column - copy_range.Cells(1).Column
copy_range.Copy dest_range
For Each nm In wb_copy.Names
If Not Intersect(nm.RefersToRange, copy_range) Is Nothing Then
wb_dest.Names.Add Name:=nm.Name, RefersTo:=ws_dest.Range(nm.RefersToRange.Offset( _
offset_row, offset_col).Address)
End If
Next
End Function
Use like:
sub macro_1()
copy_named_range Sheet1.Range("A1:A10"), Sheet2.Range("C1:C10")
end sub
Bookmarks