Need to Consolidate multiple workbooks in to a Master Workbook w/ files on SharePoint

I have no way to test this at all, so it's a hope to get you close enough you can work out the rest. This macro goes IN the master workbook with all the sheets that match the name of the files on your Sharepoint, you'll need to edit the path to your Sharepoint files in the macro:

Option Explicit

Sub ImportFromSharePoint()
Dim sharePATH As String, fNAME As String
Dim wb As Workbook, ws As Worksheet

sharePATH = "http://test:8080/PWA/Project Documents/"

For Each ws In ThisWorkbook.Worksheets
    fNAME = sharePATH & ws.Name & ".xls"
    If Workbooks.CanCheckOut(fNAME) = True Then
        Workbooks.CheckOut fNAME
        Set wb = Workbooks.Open(fNAME, , False)
        wb.Sheets(ws.Name).UsedRange.Copy ws.Range("A" & Rows.Count).End(xlUp).Offset(1)
        If Workbooks(ws.Name).CanCheckIn = True Then Workbooks(ws.Name).CheckIn False
    Else
        MsgBox "Unable to check out the file '" & ws.Name & ".xls', moving on..."
    End If
Next ws

End Sub

For now, the macro takes all the content on the opened sheet and copies it to the bottom of the matching sheetname.