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

In this article, you are going to learn various methods including VBA to copy sheet in Excel. You will learn to copy a single worksheet or multiple worksheets at once to the same or a different workbook.

Copying sheets in Excel serves various purposes, such as consolidating data, creating templates, preserving historical data, conducting scenario analysis, generating custom reports, comparing datasets, etc.

It enables efficient data management, analysis, and customization while maintaining the integrity of the original sheets.

Download Practice Book

You can download the free Excel workbook here and practice on your own.

How to Copy Sheet in Same Workbook in Excel

Method 1: Copy Sheet by Dragging

First, select 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 Sheet Through Context Menu

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

Then, 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.

There you have your copied sheet in your desired location.

Method 3: Copy Sheet Using Excel Ribbon

While staying in your sheet to be copied, click on Home >> Format (Cell Group) >> Move or Copy Sheet…

Then, 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.

There you have your copied sheet in your desired location.

Method 4: Copy Multiple Sheets at Once

Now, if you want to copy multiple sheets at once, all you need to do is select those multiple sheets one by one by pressing CTRL, dragging the sheets to the desired location, and dropping them.

There you have your copied sheets in your desired location.

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

Method 5: Copy Sheet Using Excel VBA

There could be two ways to run a VBA macro for copying a worksheet.

Example 1: Using VBA Code

To launch Visual Basic Editor, we need to click on Visual Basic >> Developer tab.

Then, Insert a module to write the code. Finally, inside the module, we can write suitable codes to get our desired output. (If you are using VBA for the first time, you may need to add the Developer tab inside the ribbon Excel)

Write 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

There you have your desired sheet copied inside the same workbook.

Example 2: Using VBA Record 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).

Now perform the same task or in this case, copy a specific sheet following any of the methods mentioned above and click on Developer >> Stop Recording.

Now, press the shortcut keys that you assigned before or in this case, CTRL + SHIFT + D. You can see that the sheet is copied again. Now, 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 Sheet in a Different Workbook in Excel

Method 1: Copy Sheet by Dragging

First, keep both the source workbook and destination workbook open at the same time. (Do not minimize). Now select the sheet that you want to copy from the source workbook, press CTRL and drag the sheet to the desired position inside the destination workbook.

Drop it. There you have your copied sheet inside another workbook.

Method 2: Copy Sheet Through Context Menu

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

Then, 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 at the same time)

There you have your copied sheet inside another workbook.

Method 3: Copy Sheet Using Excel Ribbon

While staying in your sheet to be copied inside the source workbook, click on Home >> Format (Cell Group) >> Move or Copy Sheet…

Then, 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 at the same time)

There you have your copied sheet inside another workbook.

Method 4: Copy Multiple Sheets at Once

To copy multiple sheets at once from one workbook to another, all you need to do is select those 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.

There you have your copied sheets inside another workbook.

Read More: How to Copy Multiple Sheets to New Workbook

Method 5: Copy Sheet Using Excel VBA

Example 1: Using VBA Code

Same as before, click on Developer >> Visual Basic and insert a new module. Write 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

There you have your desired sheet copied inside another workbook.

Example 2: Using VBA Record Macro

Click on the Record Macro button from the status bar of the source workbook. 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).

Now, perform the same task or in this case, copy a specific 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 Sheet in Excel

You can move any Excel worksheet almost the same way you copied them. In case of drag and drop, just select the desired sheet(s), then drag and drop. No need to press CTRL.

And in case of right-clicking or Excel ribbon, do not check the Create a copy box inside the Move or Copy dialogue box. The rest of the procedures are the same.

Lastly, in case of the VBA codes, replace the .Copy property with .Move property and you are good to go.

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 >> Protect Group and look at 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. Before transferring or copying sheets in the event of several errors, it makes sense to check all the names. In order to do this, 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.

Conclusion

By going through this article, I hope, you can ensure a successful and accurate process to copy sheet in Excel, preserving the intended functionality and data.

To further expand your knowledge and expertise in Excel, I encourage you to visit Exceldemy, a valuable resource for in-depth information on various uses of Microsoft Excel. There, you can explore a wide range of tutorials, tips, and tricks that will empower you to leverage the full potential of this powerful spreadsheet software.

Copy Sheet in Excel: Knowledge Hub

<< Go Back to Worksheets | Learn Excel