+ Reply to Thread
Results 1 to 6 of 6

Copying sheets between files and keep links

  1. #1
    Registered User
    Join Date
    04-16-2008
    Posts
    12

    Copying sheets between files and keep links

    Hi,
    I have a big problem that makes me feel naucius . The thing is this; I have two excel files (2007 and 2008), almost identical, containing one sheet with raw data and one sheet that turns the data into a nice report (some other sheets as well but that doesnt matter I think...).
    I have now made alot of changes in the "report" sheet for this year, and would like to copy it to the 2007 file. However if i mark and copy-paste all cells, all formulas still poin to this years file with raw data. Same thing when i copy the whole sheet.
    Swapping the raw data sheets turns the "report" sheet into a big mess of reference errors...
    So, how can i copy a whole sheet and keep the links? Im sure it can be done some way...or my weekend is ruined
    Thanks in advance!
    /Johan

  2. #2
    Registered User
    Join Date
    04-17-2008
    Location
    Yorkshire
    Posts
    79
    Move the worksheet, then close the one you're moving from without saving.

  3. #3
    Registered User
    Join Date
    04-16-2008
    Posts
    12
    Hi mikeyfear; Thank you for the fast reply! When I try to copy or move the worksheet to the old file, it still points to the new file.
    I hope you understand what i meen...i want to copy the formulas exactly the way they are, but when pasted in the old file, all references point to the raw data sheet in the new file...

  4. #4
    Registered User
    Join Date
    04-17-2008
    Location
    Yorkshire
    Posts
    79
    So you want them to point to the same sheet and cell references as they did in the old workbook, but to the sheets and cells in workbook?

    i.e. Old workbook---sheet1----cell A1
    to be
    New workbook---sheet1---cell A1

    If so..

    Goto Edit, Then Links...

    Update the souce from the book you copied from, to the one you copied to?

    Thats should sort you out.

  5. #5
    Registered User
    Join Date
    04-16-2008
    Posts
    12
    Hello again mikeyfear; Im not sure rally how you meen, but the problem is solved
    And if anyone else (for some odd reason) stumbles on the same problem, heres how you can do;
    Copy the worksheet, go "edit" -> "replace" (or whatever its called), find all prefixes that point to a different file ([filename.xls]) and replace with nothing. Done.
    Maybe what you said mikeyfear works as well. Thanks for the help.
    /Johan

  6. #6
    Forum Contributor
    Join Date
    10-27-2006
    Location
    United Kingdom
    MS-Off Ver
    EXCEL 2003 (2007 at home)
    Posts
    517

    A Word of Warning

    I have often used the option of editing addresses in the manner you suggest.
    However be careful, if you have lots of entries to be changed make sure you have got the edit EXACTLY correct!!
    Otherwise you might get youself in the situation where EXCEL is complaining about each one of hundreds of incorrect addresses (one at a time) and you end up having to use taskmaster to stop EXCEL and start again.
    I always just find and replace just ONE address to check that it is OK before replacing the rest.

    Mark.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1