Add cells from infinite number of spreadsheets in a folder

[lemon123]

I need to come up with something in VBA to essentially sum corresponding cells on identically formatted excel documents and output them onto a seperate master copy. Ie Spreadsheet 1, 2 and 3 all have information in cells A1, A2, B1, B2 so would like all the A1's to sum and output onto cell A1 on the master then the same for A2 etc etc.

Relative rookie with excel VBA, any help is greatly appreciated!

 

[arthurbr]

I'm afraid Excel cannot deal with an infinite number of sheets

 

[lemon123]

Is it possible for a varying amount of spreadsheets? i.e. anything between 2 and 10?

 

[John_w]

Try this macro. Change the folderPath string as required, and it expects Spreadsheet 1, 2, etc. to be .xlsx files.

Public Sub Sum_Cells_In_Workbooks()

    Dim folderPath As String, fileName As String
    Dim masterSheet As Worksheet
    Dim openedWb As Workbook
    
    Set masterSheet = ActiveWorkbook.Worksheets(1)
    
    folderPath = "C:\path\to\folder\"
    If Right(folderPath, 1) <> Application.PathSeparator Then folderPath = folderPath & Application.PathSeparator
    
    Application.ScreenUpdating = False
    fileName = Dir(folderPath & "*.xlsx")
    Do While fileName <> vbNullString
        Set openedWb = Workbooks.Open(folderPath & fileName)
        With masterSheet
            .Range("A1").Value = .Range("A1").Value + openedWb.Worksheets(1).Range("A1").Value
            .Range("A2").Value = .Range("A2").Value + openedWb.Worksheets(1).Range("A2").Value
            .Range("B1").Value = .Range("B1").Value + openedWb.Worksheets(1).Range("B1").Value
            .Range("B2").Value = .Range("B2").Value + openedWb.Worksheets(1).Range("B2").Value
        End With
        openedWb.Close False
        fileName = Dir
    Loop
    Application.ScreenUpdating = True
    
    MsgBox "Done"

End Sub

 

[lemon123]

Thank you so much! Was also just wondering how to modify to make this work for Mac as well. Presumably this below will be the difference but unsure how to source a folder on Mac

    folderPath = "C:\path\to\folder\"
    If Right(folderPath, 1) <> Application.PathSeparator Then folderPath = folderPath & Application.PathSeparator

 

[John_w]

See https://www.rondebruin.nl/mac/mac013.htm

 

[lemon123]

Try this macro. Change the folderPath string as required, and it expects Spreadsheet 1, 2, etc. to be .xlsx files.

Public Sub Sum_Cells_In_Workbooks()

    Dim folderPath As String, fileName As String
    Dim masterSheet As Worksheet
    Dim openedWb As Workbook
    
    Set masterSheet = ActiveWorkbook.Worksheets(1)
    
    [U][I][B]folderPath = "C:\path\to\folder\"[/B][/I][/U]
    If Right(folderPath, 1) <> Application.PathSeparator Then folderPath = folderPath & Application.PathSeparator
    
    Application.ScreenUpdating = False
    fileName = Dir(folderPath & "*.xlsx")
    Do While fileName <> vbNullString
        Set openedWb = Workbooks.Open(folderPath & fileName)
        With masterSheet
            .Range("A1").Value = .Range("A1").Value + openedWb.Worksheets(1).Range("A1").Value
            .Range("A2").Value = .Range("A2").Value + openedWb.Worksheets(1).Range("A2").Value
            .Range("B1").Value = .Range("B1").Value + openedWb.Worksheets(1).Range("B1").Value
            .Range("B2").Value = .Range("B2").Value + openedWb.Worksheets(1).Range("B2").Value
        End With
        openedWb.Close False
        fileName = Dir
    Loop
    Application.ScreenUpdating = True
    
    MsgBox "Done"

End Sub

Is there any way to modify this by creating a button which has a Search Folder macro instead of requiring hardcoding of the specific folder? I have written a macro for the button (I think) but unsure how to link to this code to enable it to use the folder selected by the button. Thanks so much

 Sub FolderSelect()
    On Error GoTo Err
    Dim FileExplorer As FileDialog
    Set FileExplorer = Application.FileDialog(msoFileDialogFolderPicker)

    FileExplorer.AllowMultiSelect = False

   With FileExplorer
        If .Show = -1 Then
            [folderPath] = .SelectedItems.Item(1)
        Else
            [folderPath] = ""
        End If
    End With

Err:
    Exit Sub
End Sub

 

[John_w]

Insert my code (without the Public Sub and End Sub lines) inside the If .Show = -1 clause, replacing folderPath = "C:\...." line with folderPath = .SelectedItems.Item(1).