A.N.M. Mohaimen Shanto
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.
Similar Readings
- How to Copy Multiple Sheets to New Workbook in Excel
- How to Copy Excel Sheet to Another Sheet with Same Format
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.
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.
Further Readings
- Excel VBA to Copy Multiple Sheets to New Workbook
- How to Copy a Sheet to Another Sheet in Excel
- How Do I Duplicate a Sheet Multiple Times in Excel
- Excel VBA to Copy and Rename a Worksheet Multiple Times
- How to Copy Excel Sheet into Word
- How to Copy Sheet with VBA in Excel
- How to Copy Sheet to Another Workbook with Excel Formulas
- How to Copy Sheet to End in Excel Using VBA
- VBA to Copy Excel Worksheet to Another Workbook without Formulas
- VBA to Copy Worksheet to Another Workbook and Rename
- Excel VBA: Copy Worksheet to Another Workbook Without Opening
- [Fixed!] Move or Copy Sheet Not Working in Excel
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.
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.