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

Here’s an overview of copying a sheet.

Overview to Copy Sheet in Excel

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.

Copying Sheet by Dragging in Same Workbook

  • Drop it in the desired location.

Sheet Copied by Dragging in Same Workbook


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.

Right-Click Options for Copying Sheet

  • 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.

Move or Copy Options for Copying Sheet by Right-Clicking

  • You’ll get a copy of the sheet.

Sheet Copied by Right-Clicking in Same Workbook


Method 3 – Copy a Sheet Using the Excel Ribbon

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

Copying Sheet Using Excel Ribbon in Same Workbook

  • 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.

Move or Copy Options for Copying Sheet Using Excel Ribbon

  • You’ll get a copy.

Sheet Copied Using Excel Ribbon


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.

Copying Multiple Sheet at Once (Same Workbook)

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

Multiple Sheets Copied at Once (Same Workbook)

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

Opening Visual Basic Window

  • Insert a module to write the code.

Inserting Module Inside the Visual Basic Window

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

VBA Code for Copying Sheet in Same Workbook

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.

Sheet Copied Using Excel VBA in Same Workbook


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.

Assigning Copy Sheet Shortcuts for Record Macro (Same Workbook)

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

Stopping Recording Macro to Copy Sheet (Same Workbook)

  • 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.

Worksheet Copied Using Record Macro (Same Workbook)


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.

Copying Sheet by Dragging in Another Workbook

  • Release the keys.

Sheet Copied by Dragging in Another Workbook


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.

Right-Click Options for Copying Sheet (Another Workbook)

  • 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.

Move or Copy Options for Copying Sheet by Right-Clicking (Another Workbook)

  • Excel will copy the sheet.

Sheet Copied by Right-Clicking (Another Workbook)


Method 3 – Copy a Sheet Using the Excel Ribbon

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

Copying Sheet Using Excel Ribbon (Another Workbook)

  • 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.

Move or Copy Options for Copying Sheet Using Excel Ribbon (Another Workbook)

  • There you have your copied sheet inside another workbook.

Sheet Copied Using Excel Ribbon (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.

Copying Multiple Sheet at Once (Another Workbook)

  • Here’s the result.

Multiple Sheets Copied (Another Workbook)

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).

VBA Code for Copying Sheet in Another Workbook

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

Sheet Copied Using Excel VBA in Another Workbook


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.

Assigning Copy Sheet Shortcuts for Record Macro (Another Workbook)

  • 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.

Worksheet Copied Using Record Macro (Same Workbook)


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

Get FREE Advanced Excel Exercises with Solutions!
Md. Nafis Soumik
Md. Nafis Soumik

Md. Nafis Soumik graduated from Bangladesh University of Engineering & Technology, Dhaka, with a BSc.Engg in Naval Architecture & Marine Engineering. In January 2023, he joined Softeko as an Excel and VBA content developer, contributing 50+ articles on topics including Data Analysis, Visualization, Pivot Tables, Power Query, and VBA. Soumik participated in 2 specialized training programs on VBA and Chart & Dashboard designing in Excel. During leisure, he enjoys music, travel, and science documentaries, reflecting a diverse range... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo