Need a Macro to Create a Dynamic Index/Match among Cumulated Files

Thanks, I've added the desired "Aggregate" mockup. You'll see that it basically adds the three "Cash Flow" files together, but "Cash_Flows.xlsx" doesn't come into the equation until Oct 2019 given that the project didn't start until then. So a dynamic index/match is needed given the date ranges of each project will vary, but I need them to sum...and pull from each project file in the housing folder.

All apologies, I re-uploaded...based on you noticing that error you are understanding my objective correctly.

Good question, yes.

No worries, this one is complex to say the least. I always assumed the files would have to be held in the same folder so that is not issue, nor is the exclusivity of only holding these files.

I'm dangerous enough to change the housing folder in the macro, but for now let's assume a Desktop folder called "Project Models".

I don't get an error, but nothing happens. The three "Cash Flow" files are saved in a Desktop folder named "Project Models" and macros are enabled in the "Aggregate" file you created.

It keeps telling me that "Cash Flows.xlsx" can't be found but all three files are saved in a desktop file that I even renamed "Test" to match your original code. I've tried opening each file and resaving in said "Test" folder.

Ok, I saved the "Aggregate" file in the same folder that houses the "Cash Flow" files ('Test'). It worked!

But...as an experiment I changed some cells in one of the cash flow files and resaved it. When I re-open the Aggregate file and re-run the macro I get the same error as before?

I restarted the entire process on another computer thinking this might be a version compatibility issue but I get the same error. I posted a screenshot of this error.

So I was able to fix the code (see below) and it works nearly flawlessly...the problem now is that each time the macro runs it adds to the previous iteration,
i.e., if I run the macro again and again it accumulates the numbers.

Please Login or Register  to view this content.

Sub updateTotals()
Application.ScreenUpdating = False
Application.Calculation = xlCalculationManual
Dim wkbSource As String, sPath As String
Dim lCol As Long, desWs As Worksheet, srcWS As Worksheet, fDateCol As Long, x As Long, y As Long, strdate As String
y = 4
Set desWs = ThisWorkbook.Sheets("Summary")
sPath = Environ("USERPROFILE") & "\desktop\Test\"
wkbSource = Dir(sPath & "*.xlsx")
Do While Len(wkbSource) > 0
If wkbSource <> ThisWorkbook.Name Then
Set srcWS = Workbooks.Open(sPath & wkbSource).Sheets("Cash Flow")
With srcWS
.Range("B1").Formula = "=TEXT(LEFT(D2,FIND(""/"",TEXT(D2,""mm/dd/yyyy""),4)),""mm/dd/yy"")"
strdate = .Range("B1")
lCol = desWs.Cells(1, desWs.Columns.Count).End(xlToLeft).Column - 1
fDateCol = desWs.Rows(2).Find(Format(.Range("B1").Value2, "m/dd/yy"), LookIn:=xlValues, lookat:=xlWhole).Column
For x = fDateCol To lCol
With desWs
On Error Resume Next
.Cells(5, x) = .Cells(5, x) + srcWS.Cells(5, y)
.Cells(6, x) = .Cells(6, x) + srcWS.Cells(6, y)
.Cells(10, x) = .Cells(10, x) + srcWS.Cells(10, y)
.Cells(11, x) = .Cells(11, x) + srcWS.Cells(11, y)
y = y + 1
End With
Next x
End With
y = 4
srcWS.Parent.Close False
End If
wkbSource = Dir
Loop
Application.Calculation = xlCalculationAutomatic
Application.ScreenUpdating = True
End Sub

Please Login or Register  to view this content.

Adding that portion works perfect!

In the previous post I changed this line slightly:
Set srcWS = Workbooks.Open(sPath & wkbSource).Sheets("Cash Flow")

And I as well, this was a huge help. One more thing of course...you'll notice there's a "Begin" date in column C. How would I add that to the code where if one or more cells in that column were populated with an amount? I realize the "Begin" date would likely be at the beginning of the month rather than the end like the others, so I'm thinking those amounts (ideally) would land in the month with which they associate...in other words, if a "Cash Flow" file has a Begin amount in column C associated with 10/1/19 it would land in the 10/31/19 column on the Aggregate file.

I get the error shown in the screenshot that I attached.

Nevermind, I re-created your file path names and it seems to work perfectly. Cheers! Many thanks as always.

It seems to be one column off.

See the "New" attachments.

Perfect, so far so good...two thumbs up.

Got another doozie...let's say each "Cash Flow" file also contains a tab called "Input" (I have attached).

In the "Aggregate" file I would like to create an "Input" tab that takes the average of the light blue numbers from each file.

The dark blue numbers are formulas so they remain unchanged and would exist in this rollup of all the Input tabs in each file.

Ideally, the end product would contain the "Aggregate" tab that so far works well adding all the "Monthly Cash Flow" amounts together from each file, but in addition an "Input" tab that averages the light blue inputs from each "Input" tab.

Yes, each "Cash Flow" file would contain an "Input" tab. The "Input" tab of the "Aggregate" file would then average the light blue cells found in each "Input" tab of the "Cash Flow" files.

I will post some new files as you described here shortly.

Ok, here are the files. You'll notice the Cash Flow files have changed a bit, but your code still works.

Great, thanks so much!

Wow...so far so good...awesome as always!

One minor quirk..is there a way to eliminate the "update links" prompt from appearing? If 50 cash flow files accumulate in the folder it will appear 50 times and the user will have to click through each one.

Perfect, thanks!