+ Reply to Thread
Results 1 to 5 of 5

correcting "Linked" formulas after copying sheets to another workbook

  1. #1
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    683

    correcting "Linked" formulas after copying sheets to another workbook

    I have a macro that copies 3 sheets from Book1 to Book2
    Sheet 3 has formulas linking to Sheet1

    In the newly copied Book2 Sheet3 the formulas maintain the link to Book1
    I need to redirect them to the new Book2 in which they reside.

    So I recorded a macro replacing the Book1 name in the formula with nothing
    When I record it, it is successful, but when it is rerun it does nothing to the formulas.
    Please Login or Register  to view this content.
    So I recorded a macro replacing the Book1 name in the formula with Book2
    When I record it, it is successful, but when it is rerun it does nothing to the formulas.
    Please Login or Register  to view this content.
    How can I correct the formulas from a linked workbook to look to a worksheet itís own workbook?

  2. #2
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016
    Posts
    3,316

    Re: correcting "Linked" formulas after copying sheets to another workbook

    Please Login or Register  to view this content.
    will only perform the find and replace on the selected cells. In order to carry out the find and replace on all cells in the sheet, try

    Please Login or Register  to view this content.
    Rule 1: Never merge cells
    Rule 2: See rule 1

    "Tomorrow I'm going to be famous. All I need is a tennis racket and a hat".

  3. #3
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    683

    Re: correcting "Linked" formulas after copying sheets to another workbook

    That doesn't do it either

    (probably should have mentioned that the preceeding line is
    Please Login or Register  to view this content.
    there are only formulas in Columns B & C

  4. #4
    Forum Expert sweep's Avatar
    Join Date
    04-03-2007
    Location
    Great Sankey, Warrington, UK
    MS-Off Ver
    2003 / 2007 / 2010 / 2016
    Posts
    3,316

    Re: correcting "Linked" formulas after copying sheets to another workbook

    This works for me.

    Are the worksheet and workbook specified in the code?
    If not, is the macro being run from the workbook you want it to operate on?

  5. #5
    Forum Contributor
    Join Date
    08-14-2006
    Location
    USA
    MS-Off Ver
    2019
    Posts
    683

    Re: correcting "Linked" formulas after copying sheets to another workbook

    I'm using XL2000 now but it failed to replace at home last night in XL2007 also.

    I believe they are specified.
    It was NOT in the workbook it was being performed on but I put it there to test and it still failed.

    the entire section of code is
    Please Login or Register  to view this content.
    PS. everything else works fine except the replace command

+ 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