Excel users often find themselves stuck on a treadmill of repetitive tasks. For instance, you may need to make a copy of a worksheet to serve as a backup copy or for testing purposes. In this article I’ll describe three ways to duplicate a worksheet. Two involve menus, the second a mouse-and-keyboard trick, and the third a single line of programming code.
You may already be familiar with the first approach shown in Figure 1:
- Click Format on Excel’s Home menu.
- Choose Move or Copy Sheet.
- At this point the Move or Copy dialog box appears:
- Copying within the same workbook: Click Create a Copy and then click OK.
- Copying to another workbook: Select the workbook name from the To Book list, click Create a Copy, and then click OK.
- Moving a sheet to another workbook: Select the workbook name from the To Book list, and then click OK.
Figure 1: A traditional approach to duplicating worksheets within a workbook.
A second way to duplicate worksheets involves right-clicking on any worksheet tab, and then choosing Move or Copy from the context menu that appears. From there follow the same steps within the Move or Copy dialog box as described above.
As shown in Figure 2, personally I find it easier to use a third approach, which involves holding down the Ctrl key while I use my left-mouse button to drag a worksheet to the right. This action will duplicate a worksheet without involving any menus.
Figure 2: Hold down the Ctrl key while you drag a sheet tab to make a copy.
As shown in Figure 3, the fourth approach enables you to replicate a contiguous group of sheets:
- Click on the first worksheet you wish to replicate.
- Hold down the Shift key while you select the last sheet that you wish to copy.
- Hold down the Ctrl key while you drag the first sheet in the group to the right with your left-mouse button. In this case you must select the first sheet in the group, otherwise you’ll simply deselect another sheet. Alternatively you can use either of the first two approaches discussed in this article once you’ve grouped the worksheets.
Figure 3: You can replicate groups of sheets at once.
You’ll likely only use this fifth approach for special projects. Let’s say you need to make 12 copies of a worksheet when setting up a budget spreadsheet, or let’s say 50 copies of a spreadsheet to track activity by location.
To test the concept, let’s start with a workbook with a single sheet, with a goal of having 12 copies of the same worksheet. A one-line macro can make 11 copies of the current worksheet:
- Press Alt-F11 on your keyboard to display Excel’s Visual Basic Editor. Mac users should press Fn-Alt-F11. Although it looks like a separate program, it’s a hidden aspect of Excel that most users haven’t seen before.
- Click the View menu within the Visual Basic Editor.
- Click Immediate Window from the View menu, or press Ctrl-G on your keyboard (for Mac, Ctrl-Cmd-G).
- At this point the Immediate window will appear onscreen. This is a special area where any programming code you type will be executed immediately; hence the name.
- Type the following line of programming code into the Immediate window, exactly as written below, and then press Enter.
Do Until Sheets.Count=12: Activesheet.Copy,ActiveSheet: Loop
The line of programming code must appear as a single line within the Immediate window.
The downside of the Immediate Window is you don’t get any direct feedback if your programming code worked, other than seeing that you now have 12 copies of the worksheet within the workbook. Error prompts will appear if you press Enter when the line of code is either incomplete or contains typographical errors. You may also encounter an error if the workbook is protected by way of the Protect Workbook command on Excel’s Review menu.
- You can safely exit the Visual Basic Editor once you’ve run the line of code.
Figure 4: You can use a line of programming code to create as many copies of a worksheet as you need.
The aforementioned line of code utilizes Visual Basic for Applications in Microsoft Excel. This is known as an object-oriented programming language, so if you want a little insight as to what the macro is doing:
- Do Until sets up a loop, meaning Excel will repeat the actions until a condition is met.
- Sheets is a collection of all worksheets within the workbook. This actually includes other types of sheets as well, meaning Chart Sheets and Macro Worksheets. We could be more specific and use the Worksheets collection instead, but Sheets results in less typing.
- Count returns the number of worksheets within the collection.
- = 12 is the ultimate number of sheets that you want within the workbook. You’ll adjust this to suit your needs, such as changing it to 6 if you wanted to replicate the worksheet 5 times. This tells the loop to repeat its process until the total number of sheets matches the number you specified.
- ActiveSheet.Copy ,ActiveSheet instructs Excel to place a copy of the worksheet that you see on screen to the right. If you omit the “, ActiveSheet” portion then the macro will create individual workbooks that contain a copy of the current worksheet.
- Loop instructs Excel to start the process over again. The Do Until instruction at the start means the loop will stop on its own when the total number of sheets in the workbook matches the number you specified.
If you were to store this within a formal macro, the code might take this form:
Do Until Sheets.Count = 12
The Immediate Window only allows us to execute a single line of code at a time, so the colons allow us to string three lines of code together into a single line that can be executed.
If needed there are two ways to determine the number of worksheets presently in an Excel workbook:
- In all versions of Excel type this in the Immediate Window and press Enter: ?Sheets.Count The Immediate window will display the number of sheets in the workbook.
- In Excel 2013 and later enter this formula into any worksheet cell to determine the number of sheets in the workbook: =SHEETS()
David H. Ringstrom, CPA, is an author and nationally recognized instructor who teaches scores of webinars each year. His Excel courses are based on over 25 years of consulting and teaching experience. His mantra is “Either you work Excel, or it works you.” David offers spreadsheet and database consulting services nationwide.