How to copy Excel sheet to another Workbook (copy a worksheet to another file)


Posted: May 3, 2022

Copying an Excel sheet to another workbook is super easy.

Not only does it save you from having to recreate the worksheet, but it also ensures your data and formulas remain the same as the original.

For example, let’s say you received an Excel file from a key supplier containing price adjustments for the next two months. Within the workbook file, adjusted prices are listed on individual worksheets for each month.

When the file arrives, you can easily copy the updated pricelists into another workbook so that you can keep a history of price adjustments throughout the year.

Copying Excel sheets is such a quick process – you can copy an entire sheet to another workbook in just 6 clicks!

How to copy a worksheet in Excel

In this blog, I will explain several ways to copy an Excel sheet to another workbook.

  1. How to copy a single worksheet into another Excel workbook.
  2. How to copy multiple sheets from one Excel workbook to another.
  3. Move Excel worksheets in and between workbooks.

Each option offers different benefits. I’ll cover these in the steps below.

1. Copy a single Excel sheet to another Workbook

Let’s work through the pricelist example where we have two workbooks - Price List History and Price Adjustments.

Two workbooks open, Price list history and Price adjustments

The Price Adjustments file is the file supplied to us by our supplier. It contains updated price lists for May and June.

The Price List History file is where we are keeping a history of price lists for each month.

We now want to copy the May and June worksheets from the Price Adjustments file into the Price List History file.

By copying the new pricelists for each month into our history file we can easily create a complete copy of the worksheets, therefore avoiding the need to re-enter the data into a new worksheet.

Copy a single Excel worksheet

Let’s step through copying the May worksheet from the Price Adjustments file into the Price List History file.

To copy only one worksheet, follow the steps below.

Step 1: Open the file you want to copy from and the file you want to copy to. In our example, the Price List History file and the Price Adjustment files need to be open.

Step 2: Before we copy the May worksheet into our Price List History file we need to insert a new worksheet so that we have somewhere to paste the data. To insert a new worksheet, we will click the New sheet button to the right of the worksheet tabs.

Price List History workbook in the April worksheet tab with the mouse over the New Sheet plus button  to the right of the worksheets tab

Step 3: Now we will return to the file we want to copy the data from and click the worksheet tab we want to copy. In our example, this is the May worksheet in the Price Adjustments file.

Price Adjustments workbook in the May worksheet tab

Step 4: Now we must select the entire worksheet. To do this click the arrow in the very top-left corner of the grid area. It’s just above row 1 and to the left of column A (or press Ctrl + A).

Orange arrow pointing to an arrow in the top left corner of the grid area.

The entire worksheet will now be selected.

The entire May worksheet is selected

Step 5: Right-click the selected area and then select Copy from the shortcut menu (or press Ctrl + C to copy).

The entire May worksheet is selected in the Price adjustments file with a short menu on the right and Copy being selected from that list.

Paste the copied data into a new worksheet 

Step 6: Make your way to the new blank worksheet. In our example, this would be the new blank worksheet we have already created in the Price List History file.

Price List History worksheet in a blank worksheet named Sheet1

Step 7: Select cell A1 and then do one of the following.

  • If you want to Paste the data only once, press ENTER, and the data will now be copied into the new worksheet.
  • If you want to Paste the data multiple times, right click cell A1 and then select Paste from the shortcut menu. The data will be pasted AND remain on the Clipboard, ready for you to paste again should you wish to. 
Shortcut menu with the mouse pointing to the first paste option on the left under Paste Options:

You will now have the information from the original worksheet pasted in the new worksheet. You can now rename the sheet if you like.

Price List History workbook in the Sheet1 worksheet tab with the table pasted into it and the entire worksheet selected

Note: You must click onto cell A1 to paste the information. If you don’t, Excel will not allow you to paste the data and a warning sign will be displayed saying “To copy all cells from another worksheet into this worksheet, make sure you paste them into the first cell (A1 or R1C1).”.

Microsoft Excel Warning "To copy all cells from another worksheet to this worksheet, make sure you paste them into the first cell (A1 or R1C1) with an OK button

Copying just one sheet into another workbook is a simple process. However, you wouldn’t want to follow these steps if you were wanting to copy many worksheets into another workbook.

To copy multiple worksheets into another Excel workbook at the same time follow the steps below.

2. Copy multiple sheets from one Excel workbook to another

You can easily copy multiple sheets from one workbook file into another at the same time. This saves you from having to copy sheets one at a time.

How to copy multiple sheets in Excel

Step 1: Open the file containing the worksheets to be copied, AND the file that the worksheets will be copied to. In our example this is the workbooks Price List History and Price Adjustments.

Two workbooks open, Price List History and Price Adjustments

Step 2: In the file you want to copy the worksheets from, press Ctrl while clicking the sheet tabs for each of worksheets to be copied. In our example we will Ctrl + click the worksheets May and June in the Price Adjustments file. This will then select both worksheets for copying.

Price Adjustments workbook in the May worksheet with the mouse selecting the June worksheet

Tip: If you are wanting to copy all the sheets, right-click any worksheet tab and then click Select All Sheets.

May worksheet selected with shortcut menu and the mouse selecting the Select All Sheets option

Step 3: Right-click any selected worksheet tab and then select Move or Copy from the shortcut menu. In our example, we will right-click the May worksheet in the Price Adjustments file.

Price Adjustments workbook wit the May and June worksheets selected and a shortcut menu

Step 4: The Move or Copy dialog box will be displayed.

Move or Copy dialogue box with Price Adjustments.xlsx in To book: and May selected in Before sheet:

Step 5: From the To book drop-down list select the name of the workbook to which the selected worksheet will be copied. 

Note: this file must be open in order for it to be seen in the list. In our example we have selected the Price List History file.

Move or Copy dialogue box with a list under To Book: and Price List History.xlsx being selected

Step 6: The existing worksheets contained in the destination workbook will be displayed in the Before sheet list box.

Move or Copy dialogue box with Price List History.xlsx in To book: and a list of Jan, Feb, Mar, April and (move to end) in the Before sheet: box

Step 7: In the Before Sheet list box, select where to place the selected sheets. In our example, we want them to be at the end of all the existing worksheets so we will select (move to end).

Move or Copy dialogue box with Price List History.xlsx in To book: and (move to end) selected in the Before sheet box

Step 8: WARNING: Be sure to select the Create a Copy check box if a copy is required, otherwise the selected worksheets will be moved from the original file.

Move or Copy dialogue box with Price List History.xlsx in To book: and (move to end) selected in the Before sheet box. Create a copy is also selected.

Step 9: Click OK.

Move or Copy dialogue box with Price List History.xlsx in To book: and (move to end) selected in the Before sheet box. Create a copy is also selected. The mouse is selecting OK.

Excel will now display the file you have copied the worksheets into, with the copied worksheets placed where you wanted them.

Price List History workbook with worksheet tabs Jan, Feb, April, May and June. The worksheet tabs May and June are shown in a orange box. The workbook name is also shown in a orange box.

3. Move Excel worksheets in and between workbooks

You can also use the shortcut menu Move or Copy option to quickly move worksheets between workbooks, or to move worksheets in the same workbook. This is a much better than clicking and dragging to move a worksheet to the end of a list of 100+ sheets!

To move a worksheet or worksheets

Step 1: Open the file containing the worksheets to be moved. If you are wanting to move the worksheet into another file, open this file too.

Step 2: In the file containing the worksheet(s) to be moved, select the worksheet or worksheets you want to move. To select multiple worksheets, press Ctrl while clicking the sheet tabs for each of worksheets to be moved.

Worksheet tabs Jan, Feb, Mar, April, May and June with worksheet tabs Jan, Feb and Mar selected.

Step 3: Right-click any selected worksheet tab and then select Move or Copy. In our example, we will right-click the Mar worksheet in the Price Adjustments file.

Worksheet tabs Jan, Feb and Mar all selected with a shortcut menu beside them and the mouse selecting Move or Copy...

Step 4: The Move or Copy dialog box will be displayed.

Step 5: From the To book drop-down list select the name of the workbook to which the selected worksheet will be copied. In our example we have selected (new book) so that the selected worksheets will be moved into a new workbook file. 

Move or Copy dialogue box. Under To book:, there is Price List History.xlsx with (new book) being selected in a drop down list.

Step 6: If the workbook you are moving the sheets into contains existing worksheets, these will be displayed in the Before sheet list box. In the Before Sheet list box, select where to place the selected sheets. 

Step 7: Click OK. Excel will now display the moved the worksheets in the destination file.

Excel workbook Book2 - Excel with the workbook name shown in an orange box. Worksheet tabs Jan, Feb and March are also shown in an orange box with the Jan tab selected.

To Sum up

Using these quick and easy options to copy and move Excel worksheets between workbooks, or even in the same workbook, is going to save you a lot of time AND ensure all data and calculations within the original worksheet are an exact copy.

Pop these new skills into your Excel toolbox and you will save yourself a lot of time and energy and avoid having to recreate worksheets from scratch.

Was this blog helpful? We'd love to know. If you're a beginner or if you use Excel every day please let us know if we helped in some way by leaving a Comment below.

If you enjoyed this and want to learn more about how to use Excel, have a look at our posts Insert, rename, copy, move, hide and delete Excel worksheets and How to Protect an Excel file with password (3 quick ways to protect your work).

If you enjoyed this post check out the related posts below.

Join our free insiders Group!

Learn how to SAVE TIME and WORK SMARTER, without the 'techie' speak!

{"email":"Email address invalid","url":"Website address invalid","required":"Required field missing"}

Join our free insiders club! 

Receive 100+ Steps to Becoming Proficient in Excel

>