Excel VBA: Copying a single sheet to new excel file and save with different name

Create a new excel file and save as macro enabled file. 

Open macro or developer visual basic code editor, create module and add the below code. 

This will create a file named target.xlsx and copy the code sheet named summary to the new excel workbook.

==================================================================

Option Explicit

Sub CopySheetToNewWorkbook()

    Dim wbSource As Workbook

    Dim wbTarget As Workbook

    Dim wsSource As Worksheet

    Dim wsTarget As Worksheet

    ' Set the source workbook and worksheet

    Set wbSource = ThisWorkbook

    Set wsSource = wbSource.Sheets("summary")

    ' Create a new workbook

    Set wbTarget = Workbooks.Add

    ' Copy the source worksheet to the new workbook

    Set wsTarget = wbTarget.Sheets.Add

    wsSource.Copy Before:=wsTarget

    ' Close the source workbook if you don't need it anymore

    ' wbSource.Close SaveChanges:=False

    

    ' Store the target file

    wbTarget.SaveAs "target.xlsx"

End Sub

======================================================================