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:

  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) How to change the color of gridlines in a worksheet in Excel

2) String split in vba in Excel

3) How can I list all files in a folder using VBA?

4) How do I disable the right click option for users in my WorkBook?

5) How can I set the fill color, font color and set number format of cell to date?

6) How can I identify all cells with Conditional Formatting in my WorkSheet?

7) Vba list all files in a folder in Excel

8) How can I add a background image to a worksheet?

9) How can worksheet functions be accessed in VBA?

10) How do I enter a formula in a cell using VBA (using Absolute Reference)?

11) How can I find the number of working days between 2 dates using VBA?

12) Vba code to password protect workbook in Excel

13) How can I remove display of Gridlines in my worksheet using VBA?

14) How can I get input from user through a prompt and assign user's input to a cell?

15) How can I identify the cells that influence a particular cell to help with debugging of my spreadSheet or understand a spreadSheet that I inherited?