How to copy multiple sheets in excel to new workbook

There are a few ways to copy multiple sheets from one Excel workbook to a new workbook. Here are a few methods:

Method 1: Using the "Move or Copy" dialog box

  1. Open the workbook that contains the sheets you want to copy.
  2. Go to the "Home" tab in the ribbon.
  3. Click on the "Select All" button in the "Cells" group to select all the sheets you want to copy.
  4. Right-click on one of the selected sheets and choose "Move or Copy" from the context menu.
  5. In the "Move or Copy" dialog box, select "Create a copy" and choose a location to save the new workbook.
  6. Click "OK" to create the new workbook with the copied sheets.

Method 2: Using VBA code

  1. Open the Visual Basic Editor by pressing "Alt + F11" or by navigating to "Developer" tab in the ribbon and clicking on the "Visual Basic" button.

  2. In the Visual Basic Editor, insert a new module by clicking "Insert" > "Module" or by pressing "Alt + F11".

  3. Paste the following code into the module:

    Sub CopySheets()
 Dim srcWorkbook As Workbook
 Dim dstWorkbook As Workbook
 Dim srcSheet As Worksheet
 Dim dstSheet As Worksheet

 Set srcWorkbook = ThisWorkbook
 Set dstWorkbook = Workbooks.Add

 For Each srcSheet In srcWorkbook.Sheets
     Set dstSheet = dstWorkbook.Sheets.Add(after:=dstWorkbook.Sheets(dstWorkbook.Sheets.Count))
     dstSheet.Name = srcSheet.Name
     srcSheet.Copy Destination:=dstSheet.Range("A1")
 Next srcSheet
End Sub

  4. Save the module by clicking "File" > "Save" or by pressing "Ctrl + S".

  5. Run the code by clicking "Run" > "Run Sub/User Form" or by pressing "F5".

  6. The code will create a new workbook with the copied sheets.

Method 3: Using Power Query

  1. Open the workbook that contains the sheets you want to copy.
  2. Go to the "Data" tab in the ribbon.
  3. Click on the "From Other Sources" button in the "Get & Transform Data" group.
  4. Select "From Microsoft Query" and click "OK".
  5. In the "Query Editor", click on the "New Query" button.
  6. In the "Create a new query" dialog box, select "Blank query" and click "OK".
  7. In the "Query Editor", click on the "From Workbook" button in the "Home" tab.
  8. Select the workbook that contains the sheets you want to copy and click "OK".
  9. In the "Query Editor", select the sheets you want to copy by checking the boxes next to them.
  10. Click on the "Load" button to load the selected sheets into a new workbook.

These are just a few methods to copy multiple sheets from one Excel workbook to a new workbook. You can choose the method that works best for your needs.