Software

Copy an Excel sheet from one workbook to another

Susan Harkins shows you two quick ways to copy data from one Excel workbook to another.

Copying a sheet of data from one workbook to another sounds like a complicated job. In truth, Excel has a built-in feature that makes quick work of this task, but as usual, there's more than one way to get the job done in Excel. First, I'll show you Excel's built-in route. Then, I'll show you a second method, that's just as easy.

To copy a sheet from one workbook to another, using Excel's user interface, do the following:

  1. Open the source and the target workbook. (The source workbook contains the sheet you want to copy or move. You'll move or copy the source sheet to the target workbook.)
  2. In the source workbook, navigate to the sheet you want to copy or move.
  3. Click the Home tab and then click the Format dropdown in the Cells group. Then, select Move Or Copy Sheet in the Organize Sheets section. In Word 2003, choose Move Or Copy Sheet from the Edit menu. To bypass the ribbon or menu, right-click the sheet tab in the source workbook and choose Move Or Copy from the resulting shortcut menu.
  4. In the Move Or Copy dialog, choose the target sheet from the To Book dropdown. Excel will display only the open workbooks in this list.
  5. If necessary, update the Before Sheet selection.
  6. If you want to copy rather than move the sheet, click the Create A Copy option at the bottom of the dialog.
  7. Click OK and Excel will move or copy the sheet to the target workbook.

Now, let's try a different technique to do the same thing. I won't call it a shortcut, but it's always great to have another route, just in case. With both the source and target workbooks open, do the following:

  1. Click the View tab and choose View Side By Side in the Window group. Excel will split the screen, horizontally, between the two workbooks. In Excel 2003, you'll find this option on the Window menu.
  2. Click the sheet tab in the source workbook and drag it to the target workbook. If you want to copy the sheet instead of move it, hold down the [Ctrl] key while you drag the sheet to the target workbook.

That's all there is to it! The next time you need to copy data from one workbook to another, forget the copy and paste feature - just drag the sheet.

Warning! Moving a sheet isn't without its problems-problems that might show up later. If the sheet contains a procedure that references another sheet in the source workbook, Excel will do its best to find the source workbook. If it can't, you'll get errors when you run that procedure.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

6 comments
Standss
Standss

Good old Copy and Paste is still pretty good... particularly now that you can right click and choose different ways of Pasting. Sanjay

dugoneill
dugoneill

You still need both source and target workbooks open. Right click the tab of the excel document you want to copy/move - it brings up the same move/copy option found in the ribbon mentioned above

J-R-Doe
J-R-Doe

"I don't remember the number of text characters that can go into one cell, but it is a lot more characters than are able to be copied with a simple file copy command. For a While I often gave one of our vendors excel spreadsheets that had "over stuffed" cells only to have them copy the workbook with less than complete data. I tried to show them how to get a complete copy via copying the column and pasting into the new spreadsheet workbook, however, somehow the message didn't make it to the recipient. oh'well My suggestion is that to copy a flat file workbook and see if it looks like you have transferred all of the cells contents. The utility of the flat file excel workbook was that it was easily transferred into an Access database, if desired. " Above quoted from my comment in a previous discussion "Three things you must do when you inherit an Excel workbook". For most instances the copy/move instructions are applicable. However, be aware of the limitation that I have attempted to explain. If you don't believe me, run a test for yourself...

DBlayney
DBlayney

It's also worth knowing that more than one sheet can be copied/moved at the same time; just select all sheets to be operated on first and then do the rest as described.

jpl1953es
jpl1953es

Tricky procedure. There is another similar way and more straightforward. With both workbooks open, select the sheet to be copied and make a right click over the tab name, from the pop-up menu selec "move or copy sheet", then select the destination workbook (even a new one) and select "Make a copy" box.

jbenton
jbenton

did you read the article? another point to be aware of is that all named ranges and styles will copy across too