VBA Code to Sum Specific Cell Across Multiple Workbooks

K

ktm2634

New Member
Joined
May 7, 2022
Messages
3
Office Version
  1. 365
Platform
  1. Windows
SOS!

I have thousands of Excel files in a specific folder, and I need to sum specific cells in each file (those cells are O1, O2, and O3 in every file) in a summary sheet. There is only one worksheet in each file, but the worksheet is the name of each file (so the worksheet name changes with each file rather than being Sheet1 in every file).

Appreciate any help!!
 

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.
Sort by date Sort by votes
Welcome to the MrExcel Message Board!

Assuming you want to create formulas to get the data from the worksheets in that folder. If this is the case, then try the following code.

Assumptions:
1- A folder contains the worksheets called "files", and this folder is a sub-folder where the macro file is saved. You can set the strFiles variable according to your setup.
2- The workbooks contain only but only one worksheet (otherwise, this could be solved by also providing the worksheet name as the workbook name in the formula - please ask if needed).
3- Worksheets are not password protected.

If you don't want formulas but only numeric values as totals, then see the note in the code.

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:B1").Value = Array("Workbook Name", "Total")
    
    ' 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

            ' Formula for the sum of the required range
            ' 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 = "=SUM('" & strFiles & Application.PathSeparator & "[" & fil.Name & "]" & "'!$O$1:$O$3)"

            ' Note: If you want only values then comment out the following line to convert the formula to numeric value
            'rng.Offset(, 1).Formula = rng.Offset(, 1).Value

            ' Jump to the next row
            Set rng = rng.Offset(1)
        End If
    Next fil
End Sub
 
Upvote 0
smozgur said:
Welcome to the MrExcel Message Board!

Assuming you want to create formulas to get the data from the worksheets in that folder. If this is the case, then try the following code.

Assumptions:
1- A folder contains the worksheets called "files", and this folder is a sub-folder where the macro file is saved. You can set the strFiles variable according to your setup.
2- The workbooks contain only but only one worksheet (otherwise, this could be solved by also providing the worksheet name as the workbook name in the formula - please ask if needed).
3- Worksheets are not password protected.

If you don't want formulas but only numeric values as totals, then see the note in the code.

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:B1").Value = Array("Workbook Name", "Total")
  
    ' 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

            ' Formula for the sum of the required range
            ' 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 = "=SUM('" & strFiles & Application.PathSeparator & "[" & fil.Name & "]" & "'!$O$1:$O$3)"

            ' Note: If you want only values then comment out the following line to convert the formula to numeric value
            'rng.Offset(, 1).Formula = rng.Offset(, 1).Value

            ' Jump to the next row
            Set rng = rng.Offset(1)
        End If
    Next fil
End Sub[/CODE
[/QUOTE]
Click to expand...

smozgur said:
Welcome to the MrExcel Message Board!

Assuming you want to create formulas to get the data from the worksheets in that folder. If this is the case, then try the following code.

Assumptions:
1- A folder contains the worksheets called "files", and this folder is a sub-folder where the macro file is saved. You can set the strFiles variable according to your setup.
2- The workbooks contain only but only one worksheet (otherwise, this could be solved by also providing the worksheet name as the workbook name in the formula - please ask if needed).
3- Worksheets are not password protected.

If you don't want formulas but only numeric values as totals, then see the note in the code.

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:B1").Value = Array("Workbook Name", "Total")
   
    ' 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

            ' Formula for the sum of the required range
            ' 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 = "=SUM('" & strFiles & Application.PathSeparator & "[" & fil.Name & "]" & "'!$O$1:$O$3)"

            ' Note: If you want only values then comment out the following line to convert the formula to numeric value
            'rng.Offset(, 1).Formula = rng.Offset(, 1).Value

            ' Jump to the next row
            Set rng = rng.Offset(1)
        End If
    Next fil
End Sub
Click to expand...
Thanks for the reply! You are correct that each workbook only has one worksheet. This is almost exactly what I'm looking for, but instead of summing O1:O3 in each workbook, I need to sum O1 from each workbook, O2 from each workbook, and O3 from each workbook. I uploaded an image, I'm trying to get the sums of Total Paid, Total Credit Card Payment Amount, and Total Cash Payment Amount from each workbook, if that makes sense.
 

Attachments

  • Capture.PNG
    Capture.PNG
    4.4 KB · Views: 20
Upvote 0
ktm2634 said:
instead of summing O1:O3 in each workbook, I need to sum O1 from each workbook, O2 from each workbook, and O3 from each workbook.
Click to expand...
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
 
Upvote 0
Solution
smozgur said:
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
Click to expand...
Genius. Thank you for your help on this.
 
Upvote 0
You must log in or register to reply here.

Similar threads

D
  • Question
VBA - Open Files then copy specific cells into a new row.
Replies
1
Views
214
dhubz
D
KlausW
  • Solved
copy data from several closed workbooks to one workbook.
Replies
5
Views
660
KlausW
KlausW
H
  • Solved
How to copy data from one file and paste it into a dynamically named workbook
Replies
3
Views
146
dermie_72
D
G
  • Solved
Change Macro to import files from hyperlinks
2
Replies
12
Views
374
GarnesGambit
G
KlausW
  • Question
browse to folders and subfolders an copy cell
Replies
0
Views
182
KlausW
KlausW

Forum statistics

Threads
1,214,391
Messages
6,119,249
Members
448,879
Latest member
oksanana

Share this page

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
Back
Top