Please Note: This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: Copying a Worksheet.
There are many times I have needed to duplicate a worksheet. Often it is because I have spent quite a bit of time developing a worksheet and I want to use it as a starting point for another. Excel lets you copy worksheets in the following manner:
Figure 1. The Move or Copy dialog box.
There is a faster way to copy worksheets in the same workbook. All you need to do is hold down the Ctrl key as you drag a worksheet tab to a new position in the workbook. Excel automatically copies the worksheet to the new position, leaving the old sheet intact.
There is also a "gotcha" here that you need to be aware of: Excel can sometimes balk if you try to copy a worksheet from a workbook saved in Excel's native format to a workbook that is saved in the older (pre-2007) format. This is because Excel can handle, currently, many, many more rows and columns in a worksheet than it could in earlier years. That means that when you try to transfer, Excel needs to let you know that it will truncate some data that may be in areas not supported in the earlier-formatted workbook.
ExcelTips is your source for cost-effective Microsoft Excel training. This tip (12359) applies to Microsoft Excel 2007, 2010, 2013, and 2016. You can find a version of this tip for the older menu interface of Excel here: Copying a Worksheet.
Excel Smarts for Beginners! Featuring the friendly and trusted For Dummies style, this popular guide shows beginners how to get up and running with Excel while also helping more experienced users get comfortable with the newest features. Check out Excel 2013 For Dummies today!
Do you need your macro to select all the visible worksheets (and just the visible ones)? It's not as easy as it sounds, ...
Discover MoreExcel places a limit on how many characters you can use in a worksheet name. This tip discusses that limit and provides ...
Discover MoreNeed to set up a workbook that includes a worksheet for each week of the year? Here's a couple of quick macros that can ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2016-10-30 03:45:02
Jeff Hill
I move & copy sheets in excel 2007 regularly and distribute a workbook with several sheets to people who are still using excel 2003. Not really suitable for pdf. What is the best way to copy a sheet or sheets [values only] from a excel 2007 to an excel 2003 workbook?
2016-04-11 12:10:21
Ann
Another advantage of using the Crtl key to copy worksheets is that it maintains formatting better.
2016-04-09 05:04:58
Petros
VBA code stored in worksheets (document modules) can be read from workbooks with unprotected structure (Review>Protect Workbook), even if the VBA project is password locked or unviewable. Read about the hack here:
http://www.spreadsheet1.com/read-document-vba.html
Got a version of Excel that uses the ribbon interface (Excel 2007 or later)? This site is for you! If you use an earlier version of Excel, visit our ExcelTips site focusing on the menu interface.
FREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
Copyright © 2019 Sharon Parq Associates, Inc.
Please Note:
This article is written for users of the following Microsoft Excel versions: 2007, 2010, 2013, and 2016. If you are using an earlier version (Excel 2003 or earlier), this tip may not work for you. For a version of this tip written specifically for earlier versions of Excel, click here: 
Comments