Consolidating Multiple Workbooks into one Data Range

[rolo1989]

Hi All,

I am trying to consolidate a specific data range from Mutliple workbooks into one Master sheet in the file where the code is written.

I recently read the following thread which the user aimed to do the same thing...consolidate a specific data range from multiple workbooks. (http://www.mrexcel.com/forum/showthread.php?t=507942&highlight=multiple+sources&page=2)

So I used the code that finally worked for him and adapted it BUT it is not selecting and 'summing' the correct cells. The range of cells that I want to sum data from is C8:T10 and I have it so that I can select the files that I would like to sum but it is not doing it!

I am sure it is probably something simple that I am missing, please help!

The code I have so far is;

Sub consolidateall4()
    Dim DestCell As Range
    Dim WB As Workbook
    Dim FileNames As Variant
    Dim N As Long
    Dim Temp As String
    Set DestCell = ThisWorkbook.Worksheets("Master").Range("C8:T10")
    FileNames = Application.GetOpenFilename( _
        filefilter:="Excel Files (*.xls*),*.xls*", _
        Title:="Choose File", 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.
    Application.ScreenUpdating = False
    For N = LBound(FileNames) To UBound(FileNames)
'       Open the workbook
        Set WB = Workbooks.Open(Filename:=FileNames(N), ReadOnly:=True)
        Temp = Temp & ("'" & WB.Path & "\[" & WB.Name & "]Q1'!R12C3:R32C6") & ","
'       Close the workbook
        WB.Close savechanges:=False
    Next N
    DestCell.Consolidate _
            Sources:=Split(Left(Temp, Len(Temp) - 1), ","), _
            Function:=xlSum
            
    Application.ScreenUpdating = True
    ActiveWorkbook.Sheets("Master").Select
End Sub