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.

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".

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.

Any ideas?

maybe try something like:

Please Login or Register  to view this content.