Copying sheets that have many named ranges
Copying sheets that have many named ranges
(OP)
thread770-361102: Copying sheets that have many named ranges
Copying sheet names from one sheet to another
This has no error checking in the macro, so assumes you will type the sheet (tab) names in correctly.
Copying sheet names from one sheet to another
CODE
Sub named_region()
MY_SOURCE = InputBox("Enter Source Sheet Name")
MY_DEST = InputBox("Enter Destination Sheet Name")
Sheets(MY_SOURCE).Range("AA1").Select
With Selection.ListNames
For MY_ROWS = 1 To ActiveSheet.UsedRange.Rows.Count
MY_RANGE_NAME = Range("AA" & MY_ROWS).Value & "1"
MY_ADDRESS = Mid(Range("AB" & MY_ROWS).Value, 2, Len(MY_SOURCE))
MY_RANGE = Mid(Range("AB" & MY_ROWS).Value, Len(MY_SOURCE) + 3, Len(Range("AB" & MY_ROWS).Value))
a = Left(MY_ADDRESS, Len(MY_SOURCE) + 3)
If Left(MY_ADDRESS, Len(MY_SOURCE) + 3) = MY_SOURCE Then
ActiveWorkbook.Names.Add Name:=MY_RANGE_NAME, RefersTo:="=" & MY_DEST & "!" & MY_RANGE
End If
Next MY_ROWS
End With
Sheets(MY_SOURCE).Columns("AA:AB").ClearContents
End Sub This has no error checking in the macro, so assumes you will type the sheet (tab) names in correctly.
----------------------------------
Hope this helps.
----------------------------------
been away for quite a while
but am now back




