Vba save worksheet as new workbook in Excel
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
- ''
- ActiveSheet.Copy
- ActiveWorkBook.SaveAs sOutputFolderPath & sFileName, FileFormat:=xlOpenXMLWorkBook
- ActiveWorkBook.Close Savechanges:=False
- End Sub
Description:
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.
You can find similar Excel Questions and Answer hereunder
1) Vba length of an array in Excel
2) How can I find the number of working days between 2 dates using VBA?
3) How can I identify all cells with Conditional Formatting in my WorkSheet?
4) Tables in Excel VBA. How you can create table in VBA and work with them with macros
5) Here a explanation about the global seek function in VBA. Goal Seek is another tool under What If analysis that does a unique function as Scenario Manager.
6) How to print a worksheet in Excel VBA
7) How can I remove display of Gridlines in my worksheet using VBA?
8) Vba list all files in a folder in Excel
9) How can I delete all shapes in a WorkSheet?
10) How can I set the fill color, font color and set number format of cell to date?