This is our sample dataset.
We want to show the Month and Demand on a new worksheet named “Demands Only” as shown in the image below.
We can see the Total of demands is calculated using the SUM function.
The data on this worksheet comes from the original worksheet named “Sales In Every Month”.
These are called references and the SUM function containing cells are called linked cells. Copy and paste the data in another workbook named “Copied Without Reference”.
Those data will also contain links and references.
We want to copy the worksheet to another workbook as shown in the image above but without reference.
Method 1 – Using the Paste Values Option
Steps:
- Copy the entire dataset.
- 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 Select Value & Source Formatting.
We will have our copied data without any reference.
Read More: How to Copy a Sheet to Another Sheet in Excel
Method 2 – Use of Move or Copy and Break Link Commands
Steps:
- Go to the source file and worksheet. The desired worksheet is “Demands Only” in the “Original Data File” workbook.
- Right-click on the ‘Demand Only’ tab in the sheets section. Select Move or Copy. A dialog box will appear named Move or Copy.
- Select the destination workbook in the To book Our destination workbook is “Copied Without Reference”. Select Create a Copy at the bottom of the box.
- Press OK to create a new sheet called “Demand Only” in the “Copied Without Reference” workbook.
- 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 open.
- In the Edit Links box, select Break Links.
This will trigger a prompt warning about breaking the links. Click on Break Links.
- Click on Close in the Edit Links There are no references, only values.
Read More: How to Copy Sheet to Another Workbook with Excel Formulas
How to Copy a Worksheet to Another Workbook with Reference in Excel
Steps:
- Go to the Developer tab in the Ribbon and click on Visual Basic. Visual Basic window will open.
- Click on Insert >> Module.
- Enter 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:
- We created a SUBROUTINE named Sheet_copy.
- We declared 2 variables sh and wb.
- We set the wb variable as the destination workbook named “Copied Without Reference”.
- We declared a loop that will copy all the worksheets in the “Original Data File” workbook.
- Press the Run button in the tools bar.
- The desired worksheet is in the destination workbook.
Read More: Excel VBA to Copy and Rename a Worksheet Multiple Times
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 or the source workbook will lose the worksheet.
- After breaking links through the Break Links option, the process is irreversible. Back the sheet up if needed.
Download Practice Workbook
Related Articles
- How Do I Duplicate a Sheet Multiple Times in Excel
- How to Copy Multiple Sheets to New Workbook in Excel
- How to Copy Excel Sheet into Word
- [Fixed!] Move or Copy Sheet Not Working in Excel
- How to Copy Excel Sheet to Another Sheet with Same Format
<< Go Back to Copy Sheet | Worksheets | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
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.
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.
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.