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]

Sounds like you have manual calculation on. Check the setting in excel options.

Also the second time that you have

Application.ScreenUpdating = False
Application.DisplayAlerts = False

in your code, they should be True, not False.

 

[excelbytes]

Sounds like you have manual calculation on. Check the setting in excel options.

Also the second time that you have

Application.ScreenUpdating = False
Application.DisplayAlerts = False

in your code, they should be True, not False.

Thanks for letting me know about changing those FALSES to TRUES. However, that didn't have any affect, and I don't have Manual Calculations on. As I tried to articulate before, my formulas all compare the date in cell A with the date of one of the worksheets I pull in. When I run Evaluate Formula on that formula, I get:

But when I select that portion of the formula in the formula bar and hit F9 I get:

=IFERROR(IF(TRUE,SUM(First:Last!$E$5),""),"")

Because the formula is referring to a cell in a worksheet that hasn't been pulled in yet, when I open the workbook I get:

I understand why that happens, but not why the IF statement reacts two different ways between Evaluate Formula and F9 in the formula bar.

Any other thoughts?

 

[jasonb75]

If it is for a sheet that has been added after the formula then the sheet needs to be manually recalculated, either by pressing the f9 key, or by using the Application.Calculate instruction in your code (as the last thing before the code ends).

 

[excelbytes]

Sorry to say that still doesn't do it. Here is my current code:

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 = True
Application.DisplayAlerts = True

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

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

Worksheets("Classification Summary").Select

Range("B4:Y34").Select

Application.Calculate

Range("A1").Select


End Sub

 

[jasonb75]

Sorry, that was my error. I don't think that the dependencies will be active so the problem formulas wouldn't be flagged to calculate. I think you will need this one instead.

Application.CalculateFullRebuild

 

[excelbytes]

I changed:

Application.Calculate

to:

Application.CalculateFullRebuild

in the code I previously posted and that didn't do it...

 

[jasonb75]

I understand why that happens, but not why the IF statement reacts two different ways between Evaluate Formula and F9 in the formula bar.

Going back to something you said earlier, when you evaluate the formula is being recalculated at that point so it shows an updated value. The cell will still show the uncalculated result because calculation has not been triggered. I would have thought that full rebuild would work, unless it is different for links to external sheets. I'll dig around a bit more and see what I can find but may not reply until morning.

edit:- quick google search turned this up, not something I've had cause to use but worth trying. Put this in place of Application.CalculateFullRebuild although it is possible that one of the calculate lines may be needed in conjunction with this.

ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources

 

[excelbytes]

That gave me a runtime error 1004 and highlighted the new code you suggested I enter. Thanks for all your effort on this issue!

 

[excelbytes]

I tricked it by adding this to the code:

Range("B4:Y34").Select
Cells.Replace What:="$G$1", Replacement:="$G$1", LookAt:=xlPart, _
SearchOrder:=xlByRows, MatchCase:=False, SearchFormat:=False, _
ReplaceFormat:=False, FormulaVersion:=xlReplaceFormula2

Not the most elegant solution, but it worked. I'd still rather figure out if there was a normal VBA code to cause the formulas to run.