vba save worksheet as new workbook in Excel
Q186. In Excel, how can I save a WorkSheet as a new WorkBook using VBA?
To do it in Excel, here is the answer:
- Option Explicit
- Sub SaveWorkSheetAsWorkBook()
- Dim sOutputFolderPath As String, sFileName As String
- sOutputFolderPath = "C:\Users\Guest\Documents\TestFolder\"
- sFileName = ActiveSheet.Name
- ActiveWorkBook.SaveAs sOutputFolderPath & sFileName, FileFormat:=xlOpenXMLWorkBook
- ActiveWorkBook.Close Savechanges:=False
- End Sub
a) Line 8 - Copy the sheet that needs to be saved. When copied, a new WorkBook with the ActiveSheet is created.
b) Line 9 - New WorkBook created in Line 8 is saved in the folder specified with same name as the ActiveSheet.
c) Line 10 - Close the WorkBook that has been newly created and saved.