Bottom Line: Learn a quick shortcut to move or copy sheets between workbooks.
Skill Level: Beginner
Video Tutorial
Moving Sheets Between Workbooks
If you've ever moved a sheet from one workbook to another, you may have used the right-click menu to select Move or Copy. But you can actually drag and drop the sheet directly from one open workbook to another.
- Just click on the tab that has the sheet name on it.
- Then hold your mouse button down while you drag it over.
- And then release it wherever you want it to go in the new workbook.
This process moves the sheet completely and doesn't leave a copy behind in the “from” workbook. However, if you want to leave the sheet there and make a copy into a new sheet, the process is just as easy.
Copying a Sheet into a New Workbook
To create a copy, the process is identical to the one above, except before releasing the mouse button in the new sheet, you are going to hold down the Ctrl key.
This leaves a duplicate sheet in both workbooks.
You sill see the plus symbol (+) in the mouse cursor icon when you copy the sheet.
Moving or Copying Multiple Sheets
These methods work for moving or copying multiple sheets at once. Simply select the sheets you want to move or copy before dragging them over. You can select multiple sheets by holding down the Shift or Ctrl key while clicking on the sheets you want to work with.
Unfortunately, you cannot move or copy multiple sheets if one of the selected sheets contains an Excel Table. This is a limitation of Excel. Sheets with Tables must be moved one at a time.
Related Posts
If you're wondering how I set up the side-by-side workbooks in my video, I explain the process in this post: Tips for Split Screen in Windows & Mac
And if you'd like to build a dashboard like the one you saw in the video, you can get step-by-step instructions here: Introduction to Pivot Tables and Dashboards
Conclusion
Just a quick tip to make your life easier as you move sheets between workbooks! I hope it helps.
What are some of your favorite tips and shortcuts in Excel? I'd love to hear them in the comments.
Hi Jon,
One of the most frustrating things about moving sheets with formulas is the references back to the original file. I’ve been updating them by change the formulas by hand, but think search and replace may be a little quicker.
Sbut is there a better way? Or can I at least prep the destination file to have the same setup so the formulas can easily find them?
Thanks!
Hi Ken,
Great question!
If the destination file has the same sheets and ranges that are referenced in the formulas of the source workbook, then you can use the Edit Links window to change the source. Click the Edit Links button on the Data tab of the ribbon. Select the source file in the list then click Change Source…
This will prompt you to select a file. Browse to and select the destination file. This will change all formula links to the file you are working in.
If the destination file does not contain the same worksheets and ranges, then you will get an error message when doing this. In this case you can use Find and Replace to just replace the references for specific cells.
I hope that helps. I’ll add this to our list of posts for the future. It’s a great topic.
Thanks again and have a nice weekend!
I have searched everywhere for this solution…..thank you
Great Shotcut Jon!
Am sure this will come in use in our everyday tasks at work.
Thanks
Thanks Abdul! I’m happy to hear you will be putting this shortcut to good use.
Great tip, but how to move a sheet with a table(s) inside?
Hi Tom,
Great question! You can still use this technique to move or copy sheets that contain Excel Tables. However, you cannot move/copy MULTIPLE sheets at the same time if one of the sheets contains a Table. Unfortunately, this is a limitation of Excel. I hope Microsoft is able to fix this someday.
Thanks again and have a nice weekend!
I have the same issue, however if I move them one at time using a macro, next time the macro run, it will give an error as its refereeing to the last workbook which changes (i.e. Book1, Book2, etc…)
I trying to copy 2 sheets into a new workbook without saving it first. Haven’t been able to π
Great tip. I like the way you present all the tips and tricks. I’ve learned quite a bit already in the past couple days.
test comment
Hi Jon, I am frustrated. I’ve been able to do this technique with no issues for a year until this month. Usually, my cursor becomes a page icon when dragging the worksheet between the “from: and “to” screen. But now I get a “non-sign” icon (a circle with a slash through it) when I drag it to the “to screen. I don’t know what changed or any thing I switched on or off before this month to make this happened. Any advice?
Thank you. This was an excellent tip and worked to perfection.
Hi There. Great tip. This doesnt seem to be working for me. I have excel from office 2010.
Hi Jon,
While copying how can I overwrite the existing sheets that are having same names instead of creating “(2)” sheet?
this does not work at all
will *copying* a shared OneDrive worksheet to another workbook on OneDrive automatically link to the data together so both update simultaneously? Thanks!
Thank you. Your video been helpful to me π
Hi,
How do you copy all data inputed in one sheet to be duplicated/linked to another sheet?
I’ve done this multiple times. However, there are instances in which the destination file doesn’t appear on the drop down from the source file, so I can’t copy the sheet.
This doesn’t seem to be working for a browser/online version of Excell. Can you help?
Great tips! I never knew about the drag-and-drop method to move sheets between workbooks. Itβs such a time-saver. Thanks for sharing these quick techniques!