I understand.
Although it might be the first approach and perfectly possible, I don't think that I would open each file to get the O1, O2, and O3 cell values and calculate the total, however, I would still use formulas to get the cell values from the workbooks externally. I think this approach will be faster. There might be a better approach of course, but I focused on the initial one I posted, so wanted to use the same code with some modifications.
Here is the modified code. Please note that this version first retrieves the cell values from each workbook, then calculates the sum from these references, then creates the resulting range, and finally deletes the temporary columns since they are only used to get the information from the closed workbooks.
(However, if you need this result to be dynamic and tied to the source workbooks, then the formulas should stay and the resulting range should use the SUM formula to get the totals from these formula results referring to the individual cells in the workbooks.)
VBA Code:
Sub doIt()
' Requires Microsoft Scripting Runtime (Tool->References->Microsoft Scripting Runtime)
Dim fso As New FileSystemObject
Dim fld As Folder
Dim fil As File
Dim sht As Worksheet
Dim rng As Range
Dim strFiles As String
' Create a new worksheet
' or use the ActiveSheet
Set sht = ThisWorkbook.Worksheets.Add
' Header row
sht.Range("A1:D1").Value = Array("Workbook Name", "O1", "O2", "O3")
' Start from cell A2
Set rng = sht.Range("A2")
' Set the strFiles variable as the directory path that contains the files
' Currently using a folder named "files" in the same directory with this workbook
strFiles = ThisWorkbook.Path & Application.PathSeparator & "files"
' Create the folder object by providing the directory path
Set fld = fso.GetFolder(strFiles)
' Loop through files in the folder
For Each fil In fld.Files
' Make sure if it is an Excel file
If fil.Type = "Microsoft Excel Worksheet" Then
' File name
rng.Value = fil.Name
' Fetch the O1, O2, O3 values
' Since there is only one worksheet in each Excel file,
' there is no need to specify the worksheet name in the formula
' If there is an exception about this (multiple sheets?)
' then this method will fail since it will ask to select a worksheet
rng.Offset(, 1).Formula = "='" & strFiles & Application.PathSeparator & "[" & fil.Name & "]" & "'!$O$1"
rng.Offset(, 2).Formula = "='" & strFiles & Application.PathSeparator & "[" & fil.Name & "]" & "'!$O$2"
rng.Offset(, 3).Formula = "='" & strFiles & Application.PathSeparator & "[" & fil.Name & "]" & "'!$O$3"
' Jump to the next row
Set rng = rng.Offset(1)
End If
Next fil
' Array variable to store the totals
ReDim arrResult(1 To 3, 1 To 2) As Variant
' Create the result array
' Calculate the sum of the columns for O1, O2, O3
arrResult(1, 1) = "Total Paid"
arrResult(1, 2) = Application.WorksheetFunction.Sum(rng.CurrentRegion.Columns(2))
arrResult(2, 1) = "Total CC Payment Amt"
arrResult(2, 2) = Application.WorksheetFunction.Sum(rng.CurrentRegion.Columns(3))
arrResult(3, 1) = "Total Cash Payment Amt"
arrResult(3, 2) = Application.WorksheetFunction.Sum(rng.CurrentRegion.Columns(4))
' Write the range
sht.Range("F1:G3") = arrResult
' Clean up
sht.Columns("A:E").Delete
sht.Columns.AutoFit
End Sub