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?

Sub CopyMonthlyActuals()
'
' Created 12/16/2013 by Justin Belkin
' Last updated:
'
' Override Excel default preferences
    ScreenUpdating = False
'    Application.Calculation = xlCalculationManual
    Application.DisplayAlerts = False

' Copies Actuals formulas from prior month to current month (i.e. forecast)
    LastRow = Cells(Rows.Count, "AS").End(xlUp).Row

    Range("AS3:AS" & LastRow).Select
    Application.CutCopyMode = False
    Selection.Copy
    Range("AT3:AT" & LastRow).Select
    Selection.PasteSpecial Paste:=xlPasteFormulas, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
'
    Columns("AT").Replace What:="September", Replacement:="October", SearchOrder:=xlByColumns
        
' Changes font color of current month to "green" to indicate that new Actuals reference external workbook (i.e. Monthly Financials)
    Range("AT3:AT" & LastRow).Select
        With Selection.Font
            .Color = -16744448
            .TintAndShade = 0
        End With

' Switch back to default Excel preferences
    ScreenUpdating = True
    Application.DisplayAlerts = True
'    Application.Calculation = xlCalculationAutomatic
'
End Sub