Hi,

I'm using the following code to copy worksheets based on the contents of named ranges into a new workbook where the named range is entered into an InputBox.
The first part of the procedure works fine but when it gets to the SaveAs line it throws up run-time error 1004. I can get it to work by manually entering the file name but I would like the file to be saved as the name of the range ie. myRange.

Any help is appreciated.

Sub SplitWorkbook()

Dim myArray() As String
Dim myRange As Range
Dim Cell As Range
Dim OldBook As String
Dim newBook As String

Dim a As Long

Set myRange = Application.InputBox(Prompt:="Enter Range Name", Type:=8)
'Or the range with  your sheetnames

OldBook = ActiveWorkbook.Name

For Each Cell In myRange
    If Not Cell = "" Then
        a = a + 1
        ReDim Preserve myArray(1 To a)
        myArray(a) = Cell
    End If
Next

For a = 1 To UBound(myArray)
    If a = 1 Then
        Sheets(myArray(a)).Copy
        newBook = ActiveWorkbook.Name
        Workbooks(OldBook).Activate
    Else
        Sheets(myArray(a)).Copy After:=Workbooks(newBook).Sheets(a - 1)
        Workbooks(OldBook).Activate
    End If
    
Next

Workbooks(newBook).Activate
ActiveWorkbook.SaveAs FileName:=ActiveWorkbook.Path & "\" & myRange.Name.Name, FileFormat:=51

End Sub