Vba save worksheet as new workbook in Excel

To do it in Excel, here is the answer:

  1. Option Explicit
  2. Sub SaveWorkSheetAsWorkBook()
  3. Dim sOutputFolderPath As String, sFileName As String
  4. ''
  5. sOutputFolderPath = "C:\Users\Guest\Documents\TestFolder\"
  6. sFileName = ActiveSheet.Name
  7. ''
  8. ActiveSheet.Copy
  9. ActiveWorkBook.SaveAs sOutputFolderPath & sFileName, FileFormat:=xlOpenXMLWorkBook
  10. ActiveWorkBook.Close Savechanges:=False
  11. 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?

 

Here the previous and next chapter