Sub consolidateall2()
Dim DestCell As Range
Dim DataColumn As Variant
Dim NumberOfColumns As Variant
Dim WB As Workbook
Dim DestWB As Workbook
Dim WS As Worksheet
Dim FileNames As Variant
Dim N As Long
Dim R As Range
Dim StartRow As Long
Dim LastRow As Long
Dim RowNdx As Long
Set DestWB = ActiveWorkbook
' DestCell is the first cell where the consolidated
' data will be written.
Set DestCell = DestWB.Worksheets("Sheet1").Range("C12:F32")
' DataColumn is the column on the worksheets to be
' consolidated where the actual data is. Data will
' be copied from this column.
DataColumn = "c"
' NumberOfColumns is the number of columns on each
' worksheet to be consolidated from which data will
' be copied. E.g., if your data is in range A1:J100,
' NumberOfColumns would be 10.
NumberOfColumns = 4
' StartRow is the row on the worksheets to be consolidated
' where the data starts. If your worksheet have heading/summary
' rows at the top, set this value to the row number where
' the actual data starts.
StartRow = 12
' Get the workbooks to consolidate
FileNames = Application.GetOpenFilename( _
filefilter:="Excel Files (*.xls*),*.xls*", _
Title:="Select the workbooks to merge.", MultiSelect:=True)
If IsArray(FileNames) = False Then
If FileNames = False Then
' User cancelled open dialog. get out.
Exit Sub
End If
End If
' Loop through all the selected files.
For N = LBound(FileNames) To UBound(FileNames)
' Open the workbook
Set WB = Workbooks.Open(Filename:=FileNames(N), ReadOnly:=True)
' Loop through all the worksheets in the workbook
For Each WS In WB.Worksheets
Range("C12:F32").Consolidate Array("Sheet1").Range("c12:F32"), Function:=xlSum
Next WS
' close the workbook.
WB.Close savechanges:=False
Next N
End Sub
