Copy Sheet in Excel (Copy in Same or Different Workbook)

Here’s an overview of copying a sheet.

Download the Practice Book

How to Copy a Sheet in the Same Workbook in Excel

Method 1 – Copy a Sheet by Dragging

• Select the sheet name of the sheet that you want to copy.
• Press Ctrl and drag the sheet to where you want to place the copy.

• Drop it in the desired location.

Method 2 – Copy a Sheet Through the Context Menu

• Right-click on the sheet that you want to copy.
• Select Move or Copy… from the context menu.

• Inside the dialogue box, select your workbook name under To book:, select the position of your sheet under Before sheet:, check the Create a copy box, and press OK.

• You’ll get a copy of the sheet.

Method 3 – Copy a Sheet Using the Excel Ribbon

• Click on Home and go to Format (Cell Group), then select Move or Copy Sheet…

• Inside the dialog box, select your workbook name under To book:, select the position of your sheet under Before sheet:, check the Create a copy box, and press OK.

• You’ll get a copy.

Method 4 – Copy Multiple Sheets at Once

• Press and hold Ctrl.
• Select multiple sheets one by one.
• While still holding the Ctrl button, drag the sheets to the desired location.
• Release the mouse and Ctrl buttons.

• You’ll get the copied sheets in your desired location.

Read More: How Do I Duplicate a Sheet in Excel Multiple Times

Method 5 – Copy a Sheet Using Excel VBA

Example 1 – Using VBA Code

• Click on Visual Basic in the Developer tab. If you are using VBA for the first time, you may need to add the Developer tab to the ribbon.

• Insert a module to write the code.

• Insert the following VBA code inside Module1 and click on Run Sub (Green Play Button).

Code Syntax:

Sub CopySheet()

    Dim ws As Worksheet

    Set ws = ThisWorkbook.Worksheets("English")    

    ws.Copy After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)

End Sub

• Here’s the result.

Example 2 – Using VBA to Record a Macro

• Click on the Record Macro button from the status bar.
• Inside the dialog box, specify the macro name, shortcut keys and press OK. Make sure not to set any shortcut key that is already a default shortcut key for MS Excel or any other applications on your computer.

• Copy a sheet following any of the methods mentioned above, then click on Developer and Stop Recording.

• Press the shortcut keys that you assigned before (in this case, Ctrl + Shift + D). The sheet is copied again. As the macro is already recorded and the shortcut is assigned, you can perform the task as many times as required by just pressing the shortcut keys.

How to Copy a Sheet in a Different Workbook in Excel

Method 1 – Copy a Sheet by Dragging

• Open both the source workbook and destination workbook. (Do not minimize).
• Select the sheet that you want to copy from the source workbook.
• Press and hold Ctrl and drag the sheet to the desired position inside the destination workbook.

• Release the keys.

Method 2 – Copy a Sheet Through the Context Menu

• Right-click on the sheet that you want to copy inside the source workbook.
• Select Move or Copy… in the context menu.

• Inside the dialog box, select the destination workbook name under To book:, select the position of your sheet under Before sheet:, check the Create a copy box, and press OK.  The Destination workbook will not be visible inside the list unless it is open.

• Excel will copy the sheet.

Method 3 – Copy a Sheet Using the Excel Ribbon

• Click on Home, then on Format (Cell Group), and select Move or Copy Sheet…

• Inside the dialog box, select the destination workbook name under To book:, select the position of your sheet under Before sheet:, check the Create a copy box, and press OK. The Destination workbook will not be visible inside the list unless it is open.

• There you have your copied sheet inside another workbook.

Method 4 – Copy Multiple Sheets at Once

• Select multiple sheets one by one from the source workbook by pressing Ctrl.
• Drag the sheets to the desired location inside the destination workbook and drop them.

• Here’s the result.

Read More: How to Copy Multiple Sheets to New Workbook

Method 5 – Copy Sheets Using Excel VBA

Example 1 – Using VBA Code

• Click on Developer and Visual Basic, then insert a new module.
• Use the following VBA code inside the module and press the Run Sub button (Green Play Button).

Code Syntax:

Sub CopySheetToAnotherWorkbook()

    Dim wbSource As Workbook

    Dim wbDestination As Workbook

    Dim ws As Worksheet

    Set wbSource = ThisWorkbook

    Set wbDestination = Workbooks.Open("C:\Users\User\Desktop\SOFTEKO\Article 28\Copy Sheet in Excel (2).xlsx")

    Set ws = wbSource.Worksheets("Economics")    

    ws.Copy After:=wbDestination.Sheets(wbDestination.Sheets.Count)

End Sub

Example 2 – Record a Macro

• Click on the Record Macro button from the status bar of the source workbook.
• Inside the dialog box, specify the macro name and shortcut keys and press OK. Make sure not to set any shortcut key that is already a default shortcut key for MS Excel or any other applications on your computer.

• Copy a sheet to another workbook following any of the methods mentioned above and stop the recording as before.
• Press the shortcut keys that you assigned before or in this case, Ctrl + Shift + E.
• Now you can perform the task as many times as required by just pressing the shortcut keys.

How to Move a Sheet in Excel

• Select the desired sheet(s), then drag and drop. No need to hold CTRL.
• When right-clicking or Excel ribbon, do not check the Create a copy box inside the Move or Copy dialogue box.
• For the VBA code, replace the .Copy instruction with .Move.

Copy or Move Sheet Not Working in Excel

• Worksheet Contains Excel Table: If one of the sheets in a group contains an Excel table (as opposed to a range), you won’t be able to move or replicate that group of sheets. Such worksheets should be handled separately.
• Workbook is Protected: In protected workbooks, moving and copying sheets are not permitted. Go to the Review tab and the Protect Group and check the Protect Workbook button to see if the workbook is protected. The worksheet is protected if the button is highlighted. To move or copy sheets, click that icon to unlock the workbook.
• Excel Duplicate Names: An error notice stating that a specific name already exists can appear when copying or moving a sheet between Excel files, indicating that the source and target workbooks both have tables or ranges with the same name. If there is only one mistake, choose Yes to keep the name as it is or No to change it. Use Ctrl + F3 to open the list of names in the currently open workbook. From there, you can change or delete the names.

Read More: [Fixed!] Move or Copy Sheet in Excel Not Working

Things to Remember

• Ensure that the formatting of the source sheet is compatible with the destination sheet.
• Check the formulas and cell references in the source sheet. If the formulas contain references to other sheets or workbooks, make sure those references are still valid and point to the correct locations in the copied sheet.
• If the source sheet contains conditional formatting rules, review and adjust them in the copied sheet if needed.

Frequently Asked Questions

How do I combine data from all Excel sheets into one?

To combine data from multiple Excel sheets by position, ensure that the data range on each source sheet follows a list format without any empty rows or columns within the list. Then follow these steps:

1. Open each source sheet and verify that the data is located in the same position on every sheet.
2. Navigate to your destination sheet and select the cell in the top-left corner where you want the consolidated data to appear. It is important to leave enough space to the right and below for the consolidated data.
3. Go to the Data tab and locate the Data Tools group. Click on the “Consolidate” option.
4. In the Function box, choose the function you want Excel to use for consolidating the data.
5. Proceed to each source sheet and select the data you wish to consolidate.
6. Ensure that the file paths are entered correctly in the “All references” section.
7. Once you have added the data from all the source sheets and workbooks, click “OK” to complete the consolidation process.

Can I link two Excel files?

To create an external reference in Excel, follow these steps:

1. Choose the cell or cells where you want to establish the reference.
2. Begin by typing an equal sign (=).
3. Switch to the source workbook and navigate to the worksheet that contains the desired cells you want to link.
4. Press the F3 key on your keyboard.
5. From the list that appears, select the name you wish to link to.
6. Press the Enter key to finalize the external reference.

Copy Sheet in Excel: Knowledge Hub

<< Go Back to Worksheets | Learn Excel