5 Ways to Duplicate Worksheets in Excel

Spreadsheets and graphs on a desk
xfgiro/istock

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:
    1. Copying within the same workbook: Click Create a Copy and then click OK.
    2. Copying to another workbook: Select the workbook name from the To Book list, click Create a Copy, and then click OK.
    3. Moving a sheet to another workbook: Select the workbook name from the To Book list, and then click OK.

Duplicate Worksheets 1

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.

Duplicate Worksheets 2

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.

Duplicate Worksheets 3

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:

  1. 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.
  2. Click the View menu within the Visual Basic Editor.
  3. Click Immediate Window from the View menu, or press Ctrl-G on your keyboard (for Mac, Ctrl-Cmd-G).
  4. 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.
  5. 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.

  1. You can safely exit the Visual Basic Editor once you’ve run the line of code.

Duplicate Worksheets 4

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

              ActiveSheet.Copy ,ActiveSheet

              Loop

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()

About David Ringstrom, CPA

David Ringstrom

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.

Replies

Please login or register to join the discussion.

avatar
Aug 23rd 2016 18:58

Juicy! Thanks!

Thanks (1)
avatar
By bricsa
Aug 23rd 2016 19:48

Cool, particularly the last one. I wasn't aware of the first one, either...I usually just open both workbooks and drag the tab over, holding the Ctrl key to keep the original where it was.
On the last one, I copied your text into the Immediate Window, call me lazy :) Also, there is a kind of feedback, no pop-up, which is what I am fairly sure that you were referring to...for I saw the new tabs being "created" along the left side of the VBA window, upper left, Project - VBAProject portion, and while it happens quickly, the code stays.
Great to see you posting stuff, David. Yes, I know you've been doing so for awhile, but I've been bad about keeping up.

Thanks (2)
to bricsa
Aug 24th 2016 16:47

Thank you for your kind feedback! You are correct that if you do have the Project Explorer displayed then you will see new sheets appear on the list. In the interest of not overwhelming those brand new to macros I assumed the Project Explorer wouldn't be visible, and thus no feedback. But if you have the Project Explorer shown, then you get a pleasant surprise!

Thanks (1)
avatar
Aug 24th 2016 15:10

thanks

Thanks (1)
avatar
Dec 14th 2016 02:48

Thank you David. These tips are so useful. Specially I like the second and third one. Easy to work with.

Thanks (0)
avatar
By Khaled
Apr 11th 2018 21:43

Pls how to Prevent Duplicate Number being entered across sheets

Thanks (0)
to Khaled
Apr 12th 2018 00:47

The only way to do this is to write a macro that would assign a unique name to each worksheet. The numbers appear so that each sheet has a unique name and so there's no other way around this.

Thanks (0)