[Excel VBA] 3 Ways to Create a New Workbook from Existing Sheets

2025年12月29日

There are often situations where you want to email only a completed report sheet or save specific sheets together as a separate file.

By using the VBA .Copy or .Move methods, you can easily extract sheets into a new workbook. In this article, I will explain three practical ways to create a new Excel file based on existing sheets.

1. Copy a Single Sheet to Create a New Workbook (Basic)

The most common method is to copy just one specific sheet into a new workbook.

Code and Explanation

Sub CreateNewBookFromSingleSheet()

    ' Prepare the sheet to be copied
    Dim sourceSheet As Worksheet
    Set sourceSheet = ThisWorkbook.Worksheets("MonthlyReport")

    ' --- 1. Copy the sheet ---
    ' If you omit arguments for the .Copy method, a new workbook is created automatically
    sourceSheet.Copy

    ' --- 2. Save the newly created workbook ---
    ' Immediately after copying, the new workbook becomes the active workbook
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & sourceSheet.Name & "_Single.xlsx"
    
    ' Close it if not needed after saving
    ' ActiveWorkbook.Close

    MsgBox "Created a new workbook from the '" & sourceSheet.Name & "' sheet."

End Sub

When you use the .Copy method on a Worksheet object and omit the Before or After arguments (which specify position), VBA automatically creates a new workbook containing only that copied sheet.

Immediately after the copy is executed, the new workbook becomes the ActiveWorkbook, so you can use the SaveAs method right away to save it.

2. Copy Multiple Sheets to a New Workbook at Once

It is also possible to group multiple sheets into a new workbook at once. You use the Array function to list the names of the sheets you want to copy.

Code and Explanation

Sub CreateNewBookFromMultipleSheets()

    ' --- 1. Copy multiple sheets specified by name ---
    ' List the sheet names to copy, separated by commas, inside the Array function
    ThisWorkbook.Worksheets(Array("Summary", "DataDetails", "Graph")).Copy

    ' --- 2. Save the newly created workbook ---
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\MultiSheet_Summary.xlsx"

    MsgBox "Created a new workbook from 3 sheets."

End Sub

By passing an array of sheet names to the Worksheets collection, a new workbook is created containing copies of all the specified sheets. The order of the sheets in the new workbook will match the order specified in the array.

3. “Move” a Sheet to Create a New Workbook

The major difference from the .Copy method is that the .Move method completely removes the sheet from the original workbook.

Code and Explanation

Sub CreateNewBookByMovingSheet()

    ' Sheet to move (this sheet will disappear from the original workbook after this operation)
    Dim sheetToMove As Worksheet
    Set sheetToMove = ThisWorkbook.Worksheets("ArchiveData")

    ' --- 1. Move the sheet ---
    ' If you omit arguments for the .Move method, a new workbook is created
    sheetToMove.Move
    
    ' --- 2. Save the newly created workbook ---
    ActiveWorkbook.SaveAs Filename:=ThisWorkbook.Path & "\" & sheetToMove.Name & "_MovedFile.xlsx"
    
    MsgBox "Moved the '" & sheetToMove.Name & "' sheet to a new workbook."

End Sub

Like .Copy, if you omit arguments for .Move, it creates a new workbook containing only that sheet. However, the sheet is removed from the original workbook after execution. This is useful when you want to completely separate or archive data.

Summary: How to Choose the Right Method

Method	Syntax	Original Sheet Status	Main Use Case
Single Sheet Copy	`Worksheets("Name").Copy`	Remains	When you want to share a specific sheet as a separate file.
Multiple Sheet Copy	`Worksheets(Array(...)).Copy`	Remains	When you want to distribute a set of related sheets.
Move Sheet	`Worksheets("Name").Move`	Removed	When you want to separate a sheet from the original workbook completely (e.g., archiving).