Formula Won't Run Unless Edited

[excelbytes]

I am using the following code to pull in several worksheets into one workbook. It works perfectly:

Sub CopyFiles()

Dim Path As String
Path = "C:\Users\mremp\OneDrive\Documents\Excel Stuff\Working\Dave\"

Dim FileName As String
FileName = Dir(Path & "*.xlsx")

Dim ws As Worksheet

Application.ScreenUpdating = False
Application.DisplayAlerts = False

Do While FileName <> ""
Workbooks.Open Path & FileName
For Each ws In ActiveWorkbook.Sheets
ws.Copy After:=ThisWorkbook.Worksheets(ThisWorkbook.Worksheets.Count)
Next ws
Workbooks(FileName).Close
FileName = Dir()
Loop

Worksheets(1).Delete

Application.ScreenUpdating = False
Application.DisplayAlerts = False


Sheets.Add(After:=Sheets("Classification Summary")).Name = "First"

Sheets.Add(After:=Sheets(Sheets.Count)).Name = "Last"

End Sub

As you can see it adds two new worksheets First & Last around the worksheets pulled into this workbook.

I then have a series of formulas on the Classification Summary worksheet that add the values of various cells from the worksheets that were pulled in.

One example of a formula is:

=IFERROR(IF($A4=Anes!$G$1,SUM(First:Last!$E$5),""),"")

It very simply says if the date in A4 is the same as the date in cell G1 of one of the worksheets that was pulled in, then add all the E5 cells from the worksheets between the first and last blank worksheet that I added.

Again, it all works well, with one exception. The formulas won't run unless I click on each cell with a formula and pretend to edit it by hitting F2 or clicking into the formula and hitting Enter again. Why is this happening? And if there is a logical reason, is there a simple VBA code that will touch all the formulas so they will calculate?

 

[jasonb75]

I was actually going to suggest something similar as a last resort, I had a report that was reading from another workbook that was periodically deleted and replaced and used something similar to update the formulas in that.

I'm going to look into at a bit more but in all honesty, I think that what you've done might be the safest approach.