How do you copy an Excel sheet without the data?

Say you’ve created an Excel workbook you need to distribute, but you can’t reveal the formulas you used in that workbook. We’ll show you an easy trick that allows you to copy a worksheet to another workbook and quickly remove the formulas, so only the results show.

Table of Contents Show

Never miss another update
Related content
How CPAs Can Mitigate Greatest Malpractice Risks
Confusion Over WOTC Blocks Progress
The Tax Planner’s Handbook
Replies (7)
How to Copy data from one Excel sheet to another without formula?
How do I Copy an Excel spreadsheet without losing the layout and format?

RELATED: Defining and Creating a Formula

This method allows you to move or copy worksheets from one workbook to another, but in our example we’re going to copy (not move) a worksheet to a new workbook, so we preserve the formulas in the original workbook. Note that you can also copy (or move) worksheets from one workbook to another, already existing workbook.

To create a workbook where the formulas in the worksheets are removed, open your original Excel workbook and select the tab for a worksheet that contains sensitive formulas. Right-click on that worksheet’s tab and select “Move or Copy” from the popup menu.

In the Move or Copy dialog box, select “(new book)” from the “To book” drop-down list. Select the “Create a copy” check box so there is a check mark in the box. This ensures that the worksheet will be copied to the new workbook and not moved out of the original workbook. Click “OK”.

NOTE: Any currently open workbooks are available to select in the “To book” drop-down list.

A new Excel workbook is created and the copied worksheet is pasted into the workbook, along with the name of the worksheet tab. You can save this workbook with a different name to preserve the original workbook.

Select all the cells on the worksheet you just copied into this workbook by pressing Ctrl+A or by clicking on the square in the upper-left corner of the cells, between the column letters and the row numbers.

Make sure the “Home” tab is active. In the Clipboard section, click the down arrow on the “Paste” button and then click the “Values” button in the “Paste Values” section of the drop-down menu, as shown in the image below.

The worksheet now contains only the results from the formulas in the original worksheet, not the formulas themselves.

Follow this procedure for each worksheet in the original workbook from which you want to remove the formulas. Once you’ve created your new workbook, keep it open and you can select that workbook from the “To book” drop-down list on the Move or Copy dialog box.

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.

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.

Never miss another update

Please enter your email address in order to receive our emails.

Enter email address *

Enter email address

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

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

Tags:

Excel
Tips and tutorials

Share this content

How CPAs Can Mitigate Greatest Malpractice Risks

Lauren Pitonyak

Clients

Aug 18th 2022

Confusion Over WOTC Blocks Progress

Pete Isberg

Guide

The Tax Planner’s Handbook

David Ringstrom

Author/Presenter/Spreadsheet Consultant

Accounting Advisors, Inc.

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

Comments for this post are now closed.

By JJ Collins

Aug 23rd 2016 13:58 EDT

Juicy! Thanks!

Thanks (1)

By bricsa

Aug 23rd 2016 14:48 EDT

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)

Replying to bricsa:

By David Ringstrom

Aug 24th 2016 11:47 EDT

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)

By arun garg

Aug 24th 2016 10:10 EDT

thanks

Thanks (1)

By robert_mann

Dec 13th 2016 21:48 EST

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 16:43 EDT

Pls how to Prevent Duplicate Number being entered across sheets

Thanks (0)

Replying to Khaled:

By David Ringstrom

Apr 11th 2018 19:47 EDT

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.