loop to copy data from multiple workbooks into one

Hi,

I'm trying to copy data from multiple spreadsheets into one summary spreadsheet. Each spreadsheet that I'm looking to copy data from is stored within a folder, named as a date, within a sharepoint site.

Each report is named as 'Report to PMT from Vauxhall', 'Report to PMT from Ford', 'Report to PMT from Fait' etc etc. and the format of each report is exactly the same.

From the attachments you will see that I'm trying to copy the following from the Report to PMT from Vauxhall to Summary Report:

Report to PMT from Vauxhall Cell D11 to Cell D19 of the Summary Report
Report to PMT from Vauxhall Cell E11 to Cell D19 of the Summary Report
Report to PMT from Vauxhall Cell F11 to Cell D19 of the Summary Report
Report to PMT from Ford Cell D11 to Cell D20 of the Summary Report
Report to PMT from Ford Cell E11 to Cell D20 of the Summary Report
Report to PMT from Ford Cell F11 to Cell D20 of the Summary Report
etc. etc.

Here is my current code:

Private Sub GetData() 
    Dim LookupDate As Date 
     
    LookupDate = Range("C6").Value 
    GetWorkStreamData ThisWorkbook, LookupDate, "Vauxhall" 
    GetWorkStreamData ThisWorkbook, LookupDate, "Ford" 
    GetWorkStreamData ThisWorkbook, LookupDate, "Fiat" 
    GetWorkStreamData ThisWorkbook, LookupDate, "VW" 
    GetWorkStreamData ThisWorkbook, LookupDate, "Honda" 
    GetWorkStreamData ThisWorkbook, LookupDate, "Toyota" 
     
End Sub 
 
Private Sub GetWorkStreamData(wb As Workbook, LookupDate As Date, WorkStream As String) 
     
    Const ROOT_FOLDER As String = http://sharepoint.net/meetings/reports/ 
    Dim LastRow As Long 
    Dim NextRow As Long 
     
    With wb.Sheets("WorkstreamReport") 
        NextRow = .Cells(.Rows.Count, "A").End(xlUp).Row + 1 
    End With 
     
    Workbooks.Open ROOT_FOLDER & Format(LookupDate + 4, "yyyy-mm-dd") & Application.PathSeparator & "Report to PMT from " & WorkStream & ".xls" 
     
    With ActiveSheet 
        LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row 
    End With 
     
    Rows(2).Resize(LastRow - 1).Copy wb.Sheets("WorkstreamReport").Cells(NextRow, "A") 
    ActiveWorkbook.Close savechanges:=False 
     
End Sub

Currently, this is finding the appropriate workbook to copy from, but doesnt copy anything into the summary report. Therefore, I suspect theres a big problem with the cells it is looking at and then the location of the cell it is looking to place the copied data???

Any ideas would be much appreciated.

Cheers,
Alex