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.
Create Custom Apps with VBA! Discover how to extend the capabilities of Office 2013 (Word, Excel, PowerPoint, Outlook, and Access) with VBA programming, using it for writing macros, automating Office applications, and creating custom applications. Check out Mastering VBA for Office 2013 today!
Need to sort your worksheets so that they appear in an order determined by the value of a cell on each worksheet? Using a ...
Discover MoreYour workbooks can contain many, many worksheets. Which of those worksheets are the largest, however? Here's some ideas ...
Discover MoreExcel allows you, in your formulas, to include references to cells on other worksheets. Those references include the name ...
Discover MoreFREE SERVICE: Get tips like this every week in ExcelTips, a free productivity newsletter. Enter your address and click "Subscribe."
2019-11-15 07:48:44
@Alison,
Maybe it is a bit dependant on what you want to do and how you organise your stuff.
More often than not I would be doing something along the lines of what Allan and Allen Wyatt are talking about.
Bu also, once a day, most days , I do something more along the lines of your SaveAs: I save a copy of the first worksheet of a main file. I do it with a few simple lines of coding which adds the current date to the file name of the copy
Something like this
Sub SaveAsMe_xlsx() ' https://excelribbon.tips.net/T012359_Copying_a_Worksheet.html
ThisWorkbook.Worksheets.Item(1).Copy ' This brings up a worksheet in the Excel you have open. In the next line we can save it as a File with whatever name we want
ActiveWorkbook.SaveAs Filename:="" & ThisWorkbook.Path & "\" & Replace(ThisWorkbook.Name, ".xlsm", "", 1, 1, vbBinaryCompare) & "_" & Replace(Format(Date, "dddd dd mmmm dd mm yyyy"), "ä", "ae", 1, 1, vbBinaryCompare) & ".xlsx", FileFormat:=xlOpenXMLWorkbook, CreateBackup:=False
ActiveWorkbook.Close
End Sub
Alan Elston
2019-11-14 13:38:41
Allan
Alison,
Yes ,I think you are missing something. I use Allen's method many, many times, as most of the time I want to make changes, etc. to the original Worksheet. I can also place them side-by-side to examine the changes.
2019-11-13 09:05:20
Alison J Miller
Would it not be easier to just click on file - Save As - and save it with whatever name you want? There's your copy...am I missing something?
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 © 2021 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