+ Reply to Thread
Results 1 to 7 of 7

VBA Macro Won't UpdateLinks Automatically when Formula is Copied and File Path is Changed

  1. #1
    Registered User
    Join Date
    06-24-2013
    Location
    New York
    MS-Off Ver
    Excel 2011
    Posts
    24

    VBA Macro Won't UpdateLinks Automatically when Formula is Copied and File Path is Changed

    The following macro copies over formulas that reference external workbooks (closed books for Monthly Financials) from the previous month (i.e. September) column AS to the current month (i.e. October) column AT. The file paths are almost identical month to month, except for the name of the actual month. So, the macro also does a Find/Replace to update the current month (i.e. from September to October). Finally, the font is changed to green to indicate that the new value, in fact, references an external workbook (or another worksheet, for that matter).

    With help from fellow Forum users, I was also able to get the macro to suppress that annoying Open File message box that appears for each of the 415 instances where the month September was "switched out" for October, thus changing the file path ever-so slightly.

    My persistent problem, however, is that the new values in the October column read #N/A. It appears to me that whether I have Excel set to automatic calculate or manual calculate (which is my default setting for this workbook), the true value will not appear unless I click on the cell, and then press Enter. Then, it works like a charm. But the whole purpose of the macro was to avoid having to do this 415 times manually.

    I also recorded a macro where I update the links (you'll see this integrated into the macro below), but this does not seem to work the first time. I may have read on a forum somewhere that this will not work the first time you reference a new file path, which technically is what I'm doing even though the paths are very similar.

    Would all the Excel Gurus please take a stab?

    Please Login or Register  to view this content.

  2. #2
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: VBA Macro Won't UpdateLinks Automatically when Formula is Copied and File Path is Chan

    Hi Justin,

    I haven't seen ScreenUpdating used the way you have. (But I am not a VBA guru!) I suggest you try Application.Screenupdating = False then turn it back on at the end with Application.Screenupdating = True.

    It seems to me from what you've said that the screen refresh has been successfully turned off, but is not being switched back on.


    Please remember, your request is clearer if you attach a sample workbook.
    - Click on Go Advanced and click on the Paper Clip.
    When you reply please make it clear WHO you are responding to by mentioning their name.
    If we have been of assistance, please let us know. A little thanks goes a long way.
    - Please click on the *Add Reputation button at the bottom of helpful responses.
    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".

  3. #3
    Administrator JBeaucaire's Avatar
    Join Date
    03-21-2004
    Location
    Bakersfield, CA
    MS-Off Ver
    2010, 2016, Office 365
    Posts
    33,492

    Re: VBA Macro Won't UpdateLinks Automatically when Formula is Copied and File Path is Chan

    I think it should be:
    Please Login or Register  to view this content.

    And perhaps as your final line of code, this might help:

    Please Login or Register  to view this content.
    Shot in the dark there...
    _________________
    Microsoft MVP 2010 - Excel
    Visit: Jerry Beaucaire's Excel Files & Macros

    If you've been given good help, use the icon below to give reputation feedback, it is appreciated.
    Always put your code between code tags. [CODE] your code here [/CODE]

    “None of us is as good as all of us” - Ray Kroc
    “Actually, I *am* a rocket scientist.” - JB (little ones count!)

  4. #4
    Registered User
    Join Date
    06-24-2013
    Location
    New York
    MS-Off Ver
    Excel 2011
    Posts
    24

    Re: VBA Macro Won't UpdateLinks Automatically when Formula is Copied and File Path is Chan

    Thank you for your answers so far, but unfortunately, they haven't helped solve the Run-Time 1004 error when my formula, which references another worksheet (or external workbook), has its file path changed - so that the formula link is new and (not a pre-existing link, which may affect updatelinks).

    To help illustrate my point, I have attached a macro-enabled workbook with 3 sheets. Sheet 1 is the Master Sheet with a macro button. Sheet 2 contains the September values that have already been imported into the Master Sheet through an Index/Match function. Sheet 3 contains the October values that the macro is to populate column C in the Master Sheet with.

    Interestingly, the macro does return the values I want after the macro breaks down and I press END. Nonetheless, I'm looking for someone to help me suppress the Run-Time 1004 error that appears at the line:
    Please Login or Register  to view this content.
    Attached Files Attached Files

  5. #5
    Registered User
    Join Date
    06-24-2013
    Location
    New York
    MS-Off Ver
    Excel 2011
    Posts
    24

    Re: VBA Macro Won't UpdateLinks Automatically when Formula is Copied and File Path is Chan

    Any ideas?

  6. #6
    Forum Expert
    Join Date
    04-22-2013
    Location
    .
    MS-Off Ver
    .
    Posts
    4,418

    Re: VBA Macro Won't UpdateLinks Automatically when Formula is Copied and File Path is Chan

    maybe try something like:
    Please Login or Register  to view this content.

  7. #7
    Forum Guru TMS's Avatar
    Join Date
    07-15-2010
    Location
    The Great City of Manchester, NW England ;-)
    MS-Off Ver
    MSO 2003,2007,2010
    Posts
    33,851

    Re: VBA Macro Won't UpdateLinks Automatically when Formula is Copied and File Path is Chan

    I don't think your example is going to demonstrate the problems as, for the purposes of the sample, you are referring to sheets, not external workbooks.

    You can improve the code though:

    Please Login or Register  to view this content.

    Note: with yudlugar's amendment


    Regards, TMS
    Trevor Shuttleworth - Excel Aid

    I dream of a better world where chickens can cross the road without having their motives questioned

    'Being unapologetic means never having to say you're sorry' John Cooper Clarke


+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. UpdateLinks:=1 (or3), automatically Continue!
    By gord.lawson11 in forum Excel Programming / VBA / Macros
    Replies: 9
    Last Post: 12-27-2011, 04:48 PM
  2. [SOLVED] UpdateLinks:=1 (or3), automatically Continue!
    By gord.lawson11 in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 12-27-2011, 04:06 PM
  3. Link to non-office file, update path when copied
    By Schipperus in forum Excel General
    Replies: 0
    Last Post: 07-02-2009, 09:53 AM
  4. Moved file, changed path, macros don't work
    By donbowyer in forum Excel Programming / VBA / Macros
    Replies: 3
    Last Post: 05-22-2006, 03:27 AM
  5. Replies: 0
    Last Post: 11-03-2005, 08:10 AM

Tags for this Thread

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