How to copy a sheet in Excel to another workbook

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!

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

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

Let’s work through the pricelist example where we have two workbooks - 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.

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.

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

The entire worksheet will now be selected.

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

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.

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. 

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

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

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.

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

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.

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. 

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.

Step 6: The existing worksheets contained in the destination workbook will be displayed in the Before sheet list 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).

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.

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

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.

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.

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. 

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.

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