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
Bookmarks