Formula Won't Run Unless Edited

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
145
Office Version
  1. 365
Platform
  1. Windows
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?
 

Excel Facts

Return population for a City
If you have a list of cities in A2:A100, use Data, Geography. Then =A2.Population and copy down.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,607
Office Version
  1. 365
Platform
  1. Windows
Sounds like you have manual calculation on. Check the setting in excel options.

Also the second time that you have
VBA Code:
Application.ScreenUpdating = False
Application.DisplayAlerts = False
in your code, they should be True, not False.
 

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
145
Office Version
  1. 365
Platform
  1. Windows
Sounds like you have manual calculation on. Check the setting in excel options.

Also the second time that you have
VBA Code:
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:

Screenshot 2021-11-13 155212.png


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:

Screenshot 2021-11-13 155610.png


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

Well-known Member
Joined
Dec 30, 2008
Messages
14,607
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 11, 2014
Messages
145
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

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

Well-known Member
Joined
Dec 30, 2008
Messages
14,607
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
Application.CalculateFullRebuild
 

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
145
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

I changed:

Application.Calculate

to:

Application.CalculateFullRebuild

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

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
14,607
Office Version
  1. 365
Platform
  1. Windows
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.
VBA Code:
ActiveWorkbook.UpdateLink Name:=ActiveWorkbook.LinkSources
 

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
145
Office Version
  1. 365
Platform
  1. Windows
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

Board Regular
Joined
Dec 11, 2014
Messages
145
Office Version
  1. 365
Platform
  1. Windows
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.
 
Learn Excel from Bill Jelen

Understanding data is crucial, and the easiest place to start is with Microsoft Excel.

Forum statistics

Threads
1,151,589
Messages
5,765,317
Members
425,272
Latest member
Umba

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top