Excel Copy Worksheet to Another Workbook Without Reference

One of the common scenarios in Excel is to copy an existing sheet and have it on another workbook. But the problem arises when some formulas are linked with another sheet. When you copy those sheets from that workbook and paste that 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.

Download Practice Workbook

You can download the practice workbook from here.

What Is a Worksheet with Reference?

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.

 2 Easy Ways to Copy Worksheet to Another Workbook Without Reference in Excel

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.

• Second, go to the desired workbook where you want to paste the data. In our case, the workbook is Copied Without Reference.

• Third, select any cell in the worksheet, and in the Home tab, go to Paste in the Clipboard Select Value & Source Formatting there like shown below.

Now we will have our copied data without any reference.

Similar Readings

• How to Copy Multiple Sheets in Excel to New Workbook
• Copy Excel Sheet to Another Sheet with Same Format
• How to Copy a Worksheet in Excel (5 Smart Ways)

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 are 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 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 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 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 the 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. Microsoft Visual Basic for Application 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

• 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.

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. For any excel-related problems, you can visit our website Exceldemy for solutions.

Further Readings

• ‌How to Copy a Sheet in Excel (5 Ways)
• Excel VBA to Copy Multiple Sheets to New Workbook
• How to Copy Excel Sheet to Another Sheet (5 Ways)
• Copy Excel Sheet with Formulas to Another Workbook