Iam using the following code to save a range in the active workbook into a new workbook.
Sub OutputN(SheetName As String, n, FilePath, SaveName As String, FileType)
ThisWorkbook.Sheets(SheetName).Activate
' On Error Resume Next
ThisWorkbook.Sheets(SheetName).Range(SheetName & "_Summary" & n).Copy
'Application.DisplayAlerts = False
Workbooks.Add
Selection.PasteSpecial Paste:=xlPasteValues
If FileType = xlOpenXMLWorkbook Then
EditSheet SheetName
End If
ActiveWorkbook.SaveAs Filename:=FilePath & SaveName, _
FileFormat:=FileType, CreateBackup:=False
ActiveWindow.Close
ThisWorkbook.Sheets(SheetName).Activate
Application.DisplayAlerts = True
End Sub
This works fine as long as the line EditSheet SheetName is excluded. If it is used then I get a message "subscript out of range" because of course there is no sheet called sheetname in the new workbook.
I am sure there must be a simple way out of this out of this corner, but I can't find it.
Can anyone help please.
John
Bookmarks