5 Simple Ways to Copy & Paste Multiple Excel Sheets

When working with extensive data sets in Microsoft Excel, managing multiple sheets can become a tedious task. Fortunately, Excel offers several efficient ways to copy and paste multiple sheets which can save you a significant amount of time. Here are five straightforward methods to streamline your workflow:

Method 1: Drag and Drop for Multiple Sheets

This method is intuitive and uses Excel’s interface directly for moving or copying multiple sheets within the same workbook:

• Select the sheet tabs you want to copy by holding down the Ctrl key and clicking on each tab.
• Once selected, press and hold the Alt key (Windows) or Option (Mac), and drag the sheets to a new location within the workbook.
• When you release, a menu will appear asking if you want to “Copy” or “Move” the sheets. Choose “Copy”.

💡 Note: This method doesn’t work across different workbooks.

Method 2: Using Keyboard Shortcuts

For those who prefer keyboard shortcuts, this method is a quick way to copy multiple sheets:

• Select the sheets as explained above.
• Press Ctrl + C (Windows) or Command + C (Mac).
• Navigate to the desired workbook or click on a new tab where you want to insert the sheets.
• Press Ctrl + V (Windows) or Command + V (Mac) to paste.

Method 3: Copy Sheet Using the Context Menu

This method uses Excel’s right-click context menu for an easy copy-paste:

• Select the sheets you want to copy.
• Right-click on one of the selected sheet tabs.
• Select “Move or Copy…” from the dropdown.
• In the dialog box, choose the workbook (current or different), where to insert the sheets, and check the “Create a copy” box.

Method 4: VBA Macro for Batch Copying

For repeated tasks or working with many sheets, a VBA macro can automate the process:

• Open Excel’s VBA editor with Alt + F11 (Windows) or Option + F11 (Mac).
• Insert a new module and paste the following code:

Sub CopyMultipleSheets()
    Dim wsSource As Worksheet, wsDestination As Workbook
    Dim wsToCopy() As String
    Dim i As Integer, j As Integer

'Sheets to copy
wsToCopy = Array("Sheet1", "Sheet2", "Sheet3")

'Open destination workbook
Set wsDestination = Workbooks.Open("Path_to_Destination_File.xlsx")

'Loop through sheets to copy
For i = LBound(wsToCopy) To UBound(wsToCopy)
    For j = 1 To ThisWorkbook.Sheets.Count
        If ThisWorkbook.Sheets(j).Name = wsToCopy(i) Then
            ThisWorkbook.Sheets(j).Copy After:=wsDestination.Sheets(wsDestination.Sheets.Count)
        End If
    Next j
Next i

wsDestination.Save
wsDestination.Close


End Sub

• Customize the array with the names of the sheets you wish to copy and change the path to the destination workbook.

⚠️ Note: Always backup your files before running macros to avoid accidental data loss.

Method 5: Utilizing Power Query

Power Query (Get & Transform in older versions of Excel) provides an advanced way to copy data from multiple sheets:

• Go to the “Data” tab, click “Get Data”, then “From File”, and select “From Workbook”.
• Select the workbook with the sheets you want to copy.
• In the Navigator, check the sheets you want to import, then click “Transform Data”.
• Use the “Append Queries” option to combine data from multiple sheets into one query.
• Load the resulting query into a new or existing worksheet in your current workbook.

In summary, Excel provides various techniques to copy and paste multiple sheets, each suited for different user needs. From the simplicity of drag and drop to the advanced capabilities of Power Query, these methods offer flexibility and efficiency in managing your data. Whether you’re a beginner or an advanced user, understanding these methods can significantly enhance your productivity in Excel.

How do you select multiple sheets in Excel?

+

To select multiple sheets in Excel, hold down the Ctrl key (or Command on Mac) and click on each sheet tab you wish to select. This allows you to perform actions like copying, moving, or formatting on all selected sheets simultaneously.

Can I copy sheets to a new workbook?

+

Yes, you can copy sheets to a new workbook by using the “Move or Copy” option from the right-click menu on a selected sheet tab, choosing “Create a copy”, and selecting “New book” as the destination. Alternatively, you can use VBA or Power Query to achieve this programmatically.

What are the limitations when copying sheets?

+

Limitations include not being able to copy VBA code embedded within sheets, Excel’s performance when dealing with a very large number of sheets or extremely large data sets, and some features like dynamic ranges or named ranges might not transfer seamlessly when copied between different workbooks or structures.