CODEWITHSUNDEEP
vbaexcel
dekio
dekio

Reputation: 989

Sum multiple workbooks from multiple worksheets using VBA without choosing manually

I have been trying to sum multiple workbooks with multiple worksheets with the same format. So far, I was following this post. Although I have taken a look at this and also this links trying to get a good and short idea, the first one was working nice, so I followed.

It was going pretty well so far with the post I mentioned first. However, there are one (small) problem that I could not get the answer anywhere. How can I make the code to work without having to select the files? I have they all listed in a column in the workbook called "Main", and they are all in the same folder, however, I don't know how to get them automatically, without having to manually select.

For instance, I wanted to take the files (and their address) names in, for example, Sheet(1), Range("A1:A100") in the Workbook "Main".

Can anyone give me a hand? This is the code I'm using:

Sub Results()

Dim WS_Count As Integer 'not being used
Dim FileNameXls, f
Dim wb As Workbook, i As Integer

'locate where are the Templates and how many sheets they have
Range("Template").Select
ncol = ActiveCell.Column
Selection.End(xlToRight).Select
lastcolumn = ActiveCell.Column
numSheets = lastcolumn - ncol

'Name of the First Template
Business = Cells(2, ncol)

Windows("StressTestPlatform.xlsm").Activate

'THIS IS WHERE I'M ASKED TO SELECT THE FILES
FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", MultiSelect:=True)

If Not IsArray(FileNameXls) Then Exit Sub

Application.ScreenUpdating = False

For Each f In FileNameXls
     Set wb = Workbooks.Open(f)
     For i = 3 To numSheets
         wb.Worksheets(i).Range("C5:H93").Copy 
         'The Range must be changed accordingly to the template being used
         Workbooks("Main.xlsm").Sheets("Results").Range("C5:H93").PasteSpecial Paste:=xlPasteValues, Operation:=xlAdd, SkipBlanks:=True,     Transpose:=False
     Next i
        Application.CutCopyMode = False
        wb.Close SaveChanges:=False
Next f

    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Upvotes: 0

Views: 545

Answers (1)

Trevor Eyre
Trevor Eyre

Reputation: 737

If you have the full file paths stored in a range somewhere, why not just loop through that range and open each file?

Dim TemplateRange as Range
Dim r as Range

Set TemplateRange = ThisWorkbook.Sheets(1).Range("A1:A100")
'^^ Change this to wherever your list of files is stored.

'You can eliminate the GetOpenFilename dialog entirely
'FileNameXls = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*", MultiSelect:=True)

'Instead, just loop through the template range one by one and get
'each filename and proceed with the rest of your code as before
For Each r In TemplateRange
    FileNameXls = r.Value2
    Set wb = Workbooks.Open(FileNameXls)

    '
    'The rest of your code as before
    '
Next r

If your template range has the workbook names, but not the full file path, you'll have to do a little extra work and get the directory of your Main workbook (assuming all the other files are in the same directory), and then append the workbook name to that.

Upvotes: 1

Related Questions