How to copy only a single worksheet to another workbook using vba

excel, vba

Solution

I have 1 WorkBook("SOURCE") that contains around 20 Sheets. I want to copy only 1 particular sheet to another Workbook("TARGET") using Excel VBA. Please note that the "TARGET" Workbook doen't exist yet. It should be created at runtime.

Another Way

Sub Sample()
    '~~> Change Sheet1 to the relevant sheet
    '~~> This will create a new workbook with the relevant sheet
    ThisWorkbook.Sheets("Sheet1").Copy

    '~~> Save the new workbook
    ActiveWorkbook.SaveAs "C:\Target.xlsx", FileFormat:=51
End Sub

This will automatically create a new workbook called Target.xlsx with the relevant sheet

Problem

I have 1 `WorkBook("SOURCE")` that contains around 20 Sheets. I want to copy only 1 particular sheet to another `Workbook("TARGET")` using Excel VBA. Please note that the "TARGET" Workbook doen't exist yet. It should be created at runtime. Methods Used - 1) `Activeworkbook.SaveAs` <--- Doesn't work. This will copy all the sheets. I want only specific sheet.

Original source