Copy Worksheet to Another Workbook without Reference in Excel

One of the common scenarios in Excel is to copy an existing sheet and have it on another workbook. However, the problem arises when some formulas are linked with another sheet. When you copy those sheets from that workbook and paste them on another workbook, it is automatically linked with that source workbook, or it already has some formulas in it. These source formulas or links are called references. In this tutorial, we will show you how to copy a worksheet to another workbook without reference in Excel.

What Is a Worksheet with Reference in Excel?

First, we have to understand what a worksheet is with references. Let’s take a look at this dataset in our main workbook named “Sales In Every Month”.

Here, we want to show the Month and Demand on a new worksheet named “Demands Only” like the image below.

Here we can see the Total of demands is calculated using the SUM function.

Again, we can see that the data on this worksheet comes from the original worksheet named “Sales In Every Month” as shown in the image below.

These are called references and the SUM function containing cells are called linked cells. So we will now simply copy and paste the data in another workbook named “Copied Without Reference”.

Those data will also contain links and references as shown in the image below.

So, we want to copy the worksheet to another workbook like the upper image but without reference.

Copy Worksheet to Another Workbook without Reference in Excel: 2 Easy Ways

There are two different ways to copy a worksheet to another workbook without reference or formulas in Excel. The two methods are completely different based on application and preference. Will describe both of them below with easy steps.

1. Using the Paste Values Option

This is a quick and easy method applicable for worksheets with fewer data and connections like our prior example. The steps are below.

🔶 Steps:

First, copy the entire dataset by selecting it and pressing Ctrl + C.

Go to the desired workbook where you want to paste the data. In our case, the workbook is “Copied Without Reference”.

Select any cell in the worksheet, and in the Home tab, go to Paste in the Clipboard menu. Select Value & Source Formatting from there.

Now we will have our copied data without any reference.

2. Use of Move or Copy and Break Link Commands

If we have a blank workbook and we need to copy a worksheet containing a large amount of data to another workbook without reference in Excel, this method is applicable. The steps are below.

🔶 Steps:

First, go to the source file and worksheet. Here is the desired worksheet that is “Demands Only” in the “Original Data File” workbook.

Next, we will right-click on the ‘Demand Only’ tab in the sheets section. Then select Move or Copy. A small dialog box will appear named Move or Copy.

After that, in that box, please select the destination workbook in the To book menu. Here our destination workbook is “Copied Without Reference”. Also, select Create a Copy at the bottom of the box.

Again, pressing OK will immediately create a new sheet called “Demand Only” in the “Copied Without Reference” workbook.

Now in the “Copied Without Reference” workbook, go to the Data tab in the Ribbon, and click on Edit Links in the Queries and Connection menu. A dialog box named Edit Links will appear.

Further, in the Edit Links box, select Break Links.

As a result, this will trigger a prompt warning about breaking the links. Again, click on Break Links on that prompt as well.

Finally, click on Close in the Edit Links window. Now we can see there are no references and there are values only like the Paste feature as well.

How to Copy a Worksheet to Another Workbook with Reference in Excel

So, we want to copy a worksheet to another workbook using the VBA method. Let’s say we want to copy the “Sales In Every Month” and “Demands Only” named sheets in the “Copied Without Reference” workbook from the “Original Data File” workbook. We will follow these steps.

🔶 Steps:

At first, we will go to the Developer tab in the Ribbon and click on Visual Basic. Visual Basic window will appear.

Then in the window, click on Insert >> Module.

Next in the writing space, write the following code:

Sub Sheet_copy()

Dim sh As Worksheet, wb As Workbook

Set wb = Workbooks("Copied Without Reference.xlsx")
For Each sh In Workbooks("Original Data File.xlsx").Worksheets
sh.Copy After:=wb.Sheets(wb.Sheets.Count)
Next sh

End Sub

Code Breakdown:

First, we created a SUBROUTINE named Sheet_copy.
Second, we declared 2 variables sh and wb.
Third, we set the wb variable as the destination workbook named “Copied Without Reference”.
Consequently, we declared a loop that will copy all the worksheets in the “Original Data File” workbook.

Now we will press the Run button in the tools bar.

We can see the desired worksheet is in our destination workbook.

Things to Remember

You can use any of the three options under the Paste Values section based on your preference.
For the Sheet tab options, remember to click on Create a Copy. Otherwise, the source workbook will lose the worksheet.
After breaking links through the Break Links option, the process is irreversible. Make sure to back the sheet up if needed.

Download Practice Workbook

You can download the practice workbook from here.

Original Data File.xlsx

Copied Without Reference.xlsm

Conclusion

I hope this tutorial helped you with that particular problem. Using these 2 ways will help you to copy the worksheet to another workbook without reference in Excel. If you’re still having trouble with any of these methods, let us know in the comments. Our team is ready to answer all of your questions.

<< Go Back to Copy Sheet | Worksheets | Learn Excel

Get FREE Advanced Excel Exercises with Solutions!

3 Comments

Helen Sep 12, 2022 at 11:14 PM

Is it possible to paste with formulas but without reference workbook? Currently, I’m using “find and replace” and replacing with nothing, but that trick always makes me nervous. Thank you.
- Reply
  Vee Dubby Nov 23, 2023 at 7:21 AM
  
  Adding to an old thread…
  Yes, it is possible to copy a worksheet with formulas to a destination workbook without the formulas referencing the original source workbook.
  Pre-requisite: You need to save your destination workbook first.
  Follow the steps above for “2. Use of Move or Copy and Break Link Commands”
  BUT BEFORE this step: “Further, in the Edit Links box, select Break Links”
  DO this step: “In the Edit Links box, select Change Source, locate and open the saved file of destination workbook”
  THEN you can do the step “Further, in the Edit Links box, select Break Links”
  
  By inserting the Change Source step, it tells the worksheet in the destination workbook to reference itself, and so any references to the source workbook are removed.
  You still need to do the Break Links step as the connection or link to the source workbook remains until you manually break it.
Durjoy Paul Sep 14, 2022 at 7:47 PM

Hello Helen,
You can do it without using the find and replace command. To do this follow the steps carefully.
1. First, go to the Formula tab on the ribbon.
2. Then, Select Show Formulas from the Formula Auditing section.

3. As a result, it will show you the formulas that you use in the workbook.

4. Now, copy the formulas and paste them into a notepad.

5. Then, press Ctrl+A to select all and after that, press Ctrl+C to copy.
6. In a new workbook, paste it by pressing Ctrl +V. As a consequence, you will see the formulas are copied accurately without a reference workbook.