Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

share|improve this question
    
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 –  Raystafarian Mar 6 '12 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 '12 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 '12 at 13:17
    
Okay I'm clear. I'll post an answer if/when I figure it out –  Raystafarian Mar 6 '12 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? –  WinOutreach4 Mar 6 '12 at 16:26

9 Answers 9

up vote 2 down vote accepted

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.

share|improve this answer
    
wow.. it actually works for the most part; very good trick. Thanks. –  Jake May 3 '12 at 0:58

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

copy sheet to new workbook
activate new sheet in new workbook
select all (ctrl-a)
do a find/replace on 
find:[WorkbookA.xlsx]!
replace: <leave blank>
replace all
share|improve this answer

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. Rather than using "move" or "copy" to transfer the Excel 2011 page to a new place, drag your page of formulas into the new spreadsheet.

    This leaves the formulas on the new page pointing to the old worksheet.

  3. Then go to the Edit menu and click 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.

share|improve this answer

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.

share|improve this answer

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
share|improve this answer
  • 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.

share|improve this answer

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
share|improve this answer
    
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 '12 at 2:09

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!

share|improve this answer

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”.)

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.