56

Another copy problem in Excel:

How can I copy a Worksheet from WorkbookA.xlsx into WorkbookB.xlsx without the copied Worksheet still referencing WorkbookA.xlsx e.g. the formula =B!23 becomes =[WorkbookA.xlsx]!B!23 when copied over.

I want to maintain "relative" cell references instead of "absolute" cell references (i shall invent this terminology in Excel world if it does not yet exists).

Another possible alternative that I cannot get it to work is the option to paste cell "values" only. Excel treats "values" as calculated values rather than the actual formulas in the cell. If I choose paste formula, it still gives absolute references.

More About Why I Need This: I have a production xlsx in use for daily operations. We constantly need to make "upgrades" to this xlsx and so one person may create a copy and his changes there for a single sheet. Concurrently, another person may also be making changes to another sheet. Given that these sheets have no dependant cells on other sheets, like a summary report, it is desirable for us to just copy and merge the sheets back into the original xlsx. But the "absolute" referencing is giving a lot of trouble.

7
  • I'm sort of confused. You want to copy sheet 1 from WBA into WBB and you want it to copy the formulas relative to WBB rather than WBA. This isn't possible unless WBB sheet 2 is identical to WBA sheet 2. Is that the case? Also, if you just want values you can copy the sheet select all - copy - paste special - values to have values only. If you want to do it all during the copy rather than rely on the end user to perform those steps, well, I've been working on that. Let us know exactly how you want it to go in terms of WorkbookA (or 1) and WBB(2) and the sheet #s Mar 6, 2012 at 12:35
  • I already attempted to explain in my last paragraph, but i think it still wasn't clear. Yes, WBA and WBB are identical expect for one sheet in WBA. While designing that sheet in WBA, it is intended that all cell reference be relative to the sheet's workbook. The copy worksheet function appends all cell formulas with WBA, making it "absolute" and I do not want that.
    – Jake
    Mar 6, 2012 at 13:16
  • the paste special values only paste the calculated values. I need the formulas to be copied over, WITHOUT WBA.xls appended.
    – Jake
    Mar 6, 2012 at 13:17
  • Okay I'm clear. I'll post an answer if/when I figure it out Mar 6, 2012 at 13:58
  • Can't you just make a copy of the whole workbook and then delete what you don't need from the new copy?
    – dwolters
    Mar 6, 2012 at 16:26

17 Answers 17

26

Try using Ctrl + ~ to display the formulas. Then use Ctrl + A to select everything, copy it and then paste it into notepad.

Finally, copy it out of notepad and paste it into your other workbook.

2
  • 2
    @Jake: But you have said that your workbook has buttons, dropdowns and such; and named ranges. This technique won’t copy them (or mundane things like formatting) — how/why do you consider it acceptable? Dec 8, 2016 at 20:12
  • On a large worksheet, the Edit Links/Change Source technique was painfully slow, but this was fast. Jun 25, 2019 at 20:35
31

I've found it easier, in many cases, to do the following:

  • copy the sheet to a new workbook
  • activate the new sheet in the new workbook
  • select all (Ctrl+A)
  • do a find/replace on
    • find: [WorkbookA.xlsx]!
    • replace: <leave blank>
  • replace all
1
  • It works, but there is no exclamation mark to be removed. I couldn't edit above question without editing at least 6 characters, so I'm just leaving this comment.
    – Kar.ma
    Jun 18, 2020 at 15:31
15

The unsigned answer right below this one is the one that worked for me, with a very slight variation.

  1. Create and save a destination spreadsheet.

  2. Use "move", "copy", or drag your page with the formulas into the new spreadsheet. This leaves the formulas on the new page pointing to the old worksheet. Then save the new spreadsheet in the same location as the old worksheet.

  3. Then go to the Data Tab > click Edit Links. The option won't be active unless there are links in the page.

  4. In the dialog that results, select the name of the source file and click "Change Source."

  5. From the open-file dialog that appears next, select the name of the new spreadsheet.

Click Close and you're finished.

9

Or simply do the following:

Convert this:

=database_feed!A1

to this:

=INDIRECT("database_feed!A1")

and no more changes to your references when you copy between worksheets.

If you don't have many sheets referenced, another alternative would be to use

=INDIRECT("'"&B1&"'!A1")

and enter the name of the reference sheet in cell B1. Now you only have one cell to update when copied to the new spreadsheet.

2
  • 1
    Good idea, works if you have a template sheet with formulas and you are copying this into a new workbook with VBA. I found that you need to keep the reference between quotes and add TRUE to keep A1 type references. eg. =INDIRECT("'Sheet1'!A1", TRUE)
    – RogerB
    Jun 15, 2018 at 7:08
  • 3
    INDIRECT is a volatile formula so it will be recalculated every time (as opposed to only when it's edited) which can really slow down more complex workbooks, so use with caution
    – airstrike
    Sep 4, 2018 at 2:15
8

Since 99% of the responses didn't even address the original question, here's the proper response.

  1. Copy the sheets from the original file (Original.xlsx) to the new Excel file (New.xlsx) as you normally would. Generally, I right-click on the name and choose "Move or Copy...".

  2. Save the second - newly created file (New.xlsx).

  3. In the new file, under Data, click "Edit Links"

  4. In the pop-up, choose "Change Source..."

  5. Locate the file (New.xlsx) and click Open.

All references to the original (Original.xlsx) will be removed.

DONE!

2
  • Should be higher in the list of answers
    – sabre23t
    Nov 14, 2019 at 9:56
  • Very nice. Now if it cold be automated...
    – Steev43230
    Sep 1, 2020 at 13:01
4
  • copy worksheet to 'WorkbookB.xlsx'
  • open worksheet in the new file
  • select all
  • go to menu Data, click on edit links
  • edit links so that the link to the old file is now a link to the presently open file

This works for me.

3

The code below can be adapted to your needs. It takes all the formulas from the sheet on wb1 and applies them to a sheet in a new workbook. The formulas are applied as Strings, so there's no insertion of references to the original workbook. Also, this code is super fast because it doesn't use the clipboard and doesn't require any looping through cells.

Sub copyformulas()

Dim wb1 As Workbook, wb2 As Workbook
Dim s1 As Worksheet, s2 As Worksheet
Dim formArr() As Variant

Set wb1 = ThisWorkbook
Set s1 = wb1.Sheets("Sheet1")
Set wb2 = Workbooks.Add
Set s2 = wb2.Sheets("Sheet1")

formArr = s1.UsedRange.Formula
s2.Range("A1").Resize(UBound(formArr, 1), UBound(formArr, 2)).Formula = formArr

End Sub
1
  • Does it create the sheet in the new workbook? Will it create the sheet in all workbooks in a folder? Tryna avoid doing that manually in 200+ workbooks.
    – Steev43230
    Sep 1, 2020 at 11:21
1

If you need to do this automatically because you are pulling in sheets in a VBA program. Use this:

Public Sub ChangeSource()
'
' ChangeSource Macro
' Edit the links to point to the current workbook.
'
    Dim allLinks As Variant
    allLinks = ThisWorkbook.LinkSources(xlExcelLinks)
    If Not IsEmpty(allLinks) Then
        Dim eachLink As Long
        For eachLink = 1 To UBound(allLinks)
            If InStr(3, "String found in source book name.", allLinks(eachLink)) Then
                ThisWorkbook.ChangeLink Name:=allLinks(eachLink), NewName:=ThisWorkbook.FullName, Type:=xlExcelLinks
            End If
        Next eachLink
    End If
End Sub

Just change "String found in source book name." to match the old links you want to replace. You could remove that if block if you want to replace all links.

1
  • Be nice to be able to apply this to all workbooks in a folder.
    – Steev43230
    Sep 1, 2020 at 13:03
0

Both workbooks must be open for this to work. You run this macro and it will copy workbookA!sheet1 to workbookB!sheet1 and then replace all the workbookA references. It's crude, but it works. You can obviously change the code to match your WorkbookA.xlsx names, but ensure they have the correct extension and remain in quotation marks.

Oh, to make a macro, in case you don't know, hit alt + F11 to bring up the Visual Basic Editor. Then right click on WBA insert - module and copy and paste the code below into the module. Then hit F5 to run the macro. If the macro won't run, it's probably because macros aren't enabled, so go ahead and save it and re-open and when it prompts to enable macros, enable them.

Sub copysheetremoveWBref()

    Application.ScreenUpdating = False

    'activate WBA
    Application.Workbooks("workbooka.xlsx").Activate
    'Select WBA Sheet1
    Application.Workbooks("workbooka.xlsx").Sheets("Sheet1").Select
    'copy WBA!sheet1 to WBB!sheet1
    Sheets("Sheet1").copy Before:=Workbooks("WorkbookB.xlsx").Sheets("sheet2")
    'find WBA references and remove them
    Cells.Replace What:="=[workbookA.xlsx]", Replacement:="=", LookAt:=xlPart _
        , SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
        ReplaceFormat:=False

    Application.ScreenUpdating = True


End Sub
1
  • 1
    A single routine is nice as I am manually CTRL+H now, but all these do not fix the refences for controls e.g. buttons, dropdowns and such; and named ranges.
    – Jake
    Mar 7, 2012 at 2:09
0

I achieved this by copying the cells to the new worksheet as normal, then doing a find a replace to remove the old file path in the formulas.

E.g. if the first formula is =J2 and this becomes =[filepath]J2, then just do a find and replace of the entire new spreadsheet for [filepath] and replace with nothing. This deletes it and restores the formula to =J2.

No VB required!

1
  • Sadly excel seems to only intermittently work when it comes to finding sub-strings inside of formulas :/. It doesn't seem to be capable of finding anything with single quotes either. Feb 20, 2017 at 18:09
0

I was having a similar problem. The reason the formulas were pasted with the link to WBA was that the tab (sheet) I was working on in WBA was named differently from that in WBB. For me, it was always "the last sheet" but one was named 'MinFlow' and the other was named 'NormalFlow'. I renamed both to 'Results' and the copy/paste worked as I wanted it to - a "relative paste".

0

Select cells you want to move. Now try to move them by drag n drop to different worksheet (different tab).

I know, it scrolls. Here is the tricky part: just press cmd (mac) or alt (win) and it will let you drop the cells to another tab.

1
  • Can your expand your answer a little? It isn't clear how/where to drop on another tab while dragging. Thanks.
    – fixer1234
    Apr 25, 2015 at 15:40
0

Hi here is a simple solution to this problem:

  1. Copy over the cells as usual.
  2. In the formula select and copy the text linking it to the the previous workbook [WorkbookA.xlsx].
  3. Select all cells that you want to change and Press CTRL+F and select the replace tab.
  4. Replace [WorkbookA.xlsx] with empty space (aka don't write anything in the Replace with field, press Replace All.

Voila - it is done.

0

Another 'trick' - before copying the source worksheet, replace all formula qualifiers = with other set of characters (say ###=).

Copy the worksheet over, then after copy, replace the formula qualifier back (replacing ###= with =).

Make sure that any sheets references within the formauls are also copied to the new sheet prior to the referencing sheet.

0
  1. Copy the sheet as usual. (right-click to tab and go to 'move or copy') This is for formatting.

  2. Copy all cells from original sheet (using Ctrl+A or top-left triangle and Ctrl+C)

  3. Paste as Values to the new workbook (over "step-1" sheet) (Paste Options>123)

-1

Open both workbooks.  In the source workbook (WorkbookA.xlsx), select the sheet that you want to copy.  Right-click on the sheet tab and select “Move or Copy…”.  In the “Move or Copy” dialog box, select “WorkbookB.xlsx” in the “To book” dropdown, choose where in that book you want to put it, and check “Create a copy”.  (And click “OK”.)

-1

Make a copy of sheet you want to move sheets from which is this case would be WorkbookA.xlsx. Rename it to say "Copy of WorkbookA.xlsx". Now open this new workbook as well as the workbook you want to move the sheet to which in this case would be WorkbookB.xlsx. Right click on sheets in the copy workbook you created i.e. Copy of WorkbookA.xlsx and select 'move or copy' and then move these sheets to WorkbookB.xlsx. You are done !

2
  • It's not clear how your answer solves the problem of the question. Why move will work in different way then copy would work? Oct 6, 2015 at 13:51
  • I tried your proposed solution and it does not work. There is no difference between copying and moving entire sheets.
    – nixda
    Oct 7, 2015 at 10:55

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .