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.

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.

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
Jul 23rd 2017 06:25

Great way for Duplicate Worksheets in Excel.I love this ways and this is also working.Thanks for sharing.

Thanks (0)
avatar
Aug 30th 2017 04:21

Thanks to admin for sharing Happy Diwali Status

Thanks (0)