How to save a specific sheet as a new workbook using VBA?

To save a specific sheet as a new workbook using VBA, you can follow these steps to write a macro. This macro will copy the desired sheet to a new workbook and then save that workbook as a new file. Here’s an example of the VBA code you can use:

Sub SaveSheetAsNewWorkbook()
    Dim ws As Worksheet
    Dim wb As Workbook
    Dim newWb As Workbook
    Dim sheetName As String
    Dim saveAsName As String
    Dim filePath As String

    ' Set the worksheet you want to save as a new workbook
    sheetName = "Sheet1" ' Change "Sheet1" to the name of your sheet
    Set ws = ThisWorkbook.Sheets(sheetName)

    ' Copy the sheet to a new workbook
    ws.Copy

    ' The copied sheet is now in a new workbook
    Set newWb = ActiveWorkbook

    ' Define the file path and name for the new workbook
    filePath = "C:PathToYourFolder" ' Change this to your desired path

facebooklinkedin