5 Ways to Duplicate Worksheets in Excel

Spreadsheets and graphs on a desk

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.

Please Login or Register to read the full article

To access all of the content on our site, register (it's free!) or login to your existing account.

BONUS: If you register now you can opt to receive a digital copy of "Transform!" , Richard Francis' new book for growing firms [US/Canada ONLY].

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.


Please login or register to join the discussion.

Aug 23rd 2016 18:58

Juicy! Thanks!

Thanks (1)
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)
Aug 24th 2016 15:10


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