Sub LoopThroughFiles()
Dim rng As Range
Dim shtName As String
Dim fileSelect As Variant
Dim i As Long
Dim j As Long
Dim wbkToCopy As Workbook
'Makes code run faster
Application.ScreenUpdating = False
Application.DisplayAlerts = False
Workbooks.Add
ActiveWorkbook.SaveAs Filename:="Summary" & ".xls"
ActiveSheet.Name = "Summary"
Range("A1").Value = "Header"
'Prompts user to select source files
fileSelect = Application.GetOpenFilename("Excel Files (*.xlsx), *.xlsx", Title:="Select files", MultiSelect:=True)
If IsArray(fileSelect) Then
'Loops through source files
For i = LBound(fileSelect) To UBound(fileSelect)
'Opens source file
Set wbkToCopy = Workbooks.Open(Filename:=fileSelect(i))
'Loops through each range
For j = 1 To 2
'Determines which sheet to paste in
If j = 1 Then
shtName = "16 Man Bracket"
Else
shtName = "32 Man Bracket"
End If
'Finds last row of variable sheet
LR = Workbooks("Summary.xls").Sheets("Summary").Range("A" & Rows.Count).End(xlUp).Row
'Determines which row to paste in
If j = 1 Then
'Sets range according to variable sheet
Set rng = Sheets(shtName).Range("AX20:AX25")
'Copies range from source file to Summary workbook
rng.Copy Destination:=Workbooks("Summary.xls").Sheets("Summary").Range("A" & LR + 1)
Else
'Sets range according to variable sheet
Set rng = Sheets(shtName).Range("BJ26:BJ31")
'Copies range from source file to Summary sheet
rng.Copy Destination:=Workbooks("Summary.xls").Sheets("Summary").Range("A" & LR + 1)
End If
Next j
'Closes and does NOT save source file
wbkToCopy.Close savechanges:=False
Next i
End If
MsgBox "Task Complete"
Application.ScreenUpdating = True
Application.DisplayAlerts = True
End Sub