Formula Won't Run Unless Edited

excelbytes

Board Regular
Joined
Dec 11, 2014
Messages
147
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?
 

jasonb75

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

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,151,822
Messages
5,766,648
Members
425,366
Latest member
Mau15092000

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