Macro to Copy named range of Active cell to another sheet + Name Sheet After Named Range

Hello. Try with:

PHP Code: 

Option Explicit

Sub Copy_and_name_new_worksheet()
Dim nrng As String

nrng = GetNamedRange
If nrng = "" Then MsgBox "ACTIVE CELL IS BLANK": Exit Sub
If Not Evaluate("ISREF('" & nrng & "'!A1)") Then
  Sheets.Add(, Sheets(Sheets.Count)).Name = nrng
  Range(nrng).Copy ActiveSheet.Range("A1")
Else
  MsgBox "SHEET EXISTS"
End If
End Sub

Function GetNamedRange() As String
Dim nm As Name

For Each nm In ThisWorkbook.Names
  If ActiveCell.Parent Is Range(nm.Name).Parent Then
    If Not Intersect(ActiveCell, Range(nm.Name)) Is Nothing Then
      GetNamedRange = nm.Name
      Exit Function
    End If
  End If
Next
End Function 


That's because 'Range(nm.Name)' is failing and tells you that one of the names defined in the workbook is not a cell range: Check it out!... Upload your workbook to the Forum!

.
You will need to add one more check:

PHP Code: 

Function GetNamedRange() As String
Dim nm As Name
For Each nm In ThisWorkbook.Names
  If Not IsError(Evaluate("Cell(""Row"", " & nm.Name & ")")) Then
    If ActiveCell.Parent Is Range(nm.Name).Parent Then
      If Not Intersect(ActiveCell, Range(nm.Name)) Is Nothing Then _
        GetNamedRange = nm.Name: Exit Function
    End If
  End If
Next
End Function