Copy paste from id to west column table data onto output sheet using vba

I just did the summing in the code instead of a formula on the sheet, hope that's ok. If not, just ask.

Sub globalsourcing()
Set ws1 = Sheets("output")
ws1.Cells.ClearContents
For Each ws In ThisWorkbook.Worksheets
If ws.Name <> ws1.Name Then
    Set r = ws1.Cells(Rows.Count, 1).End(3).Offset(3, 0)
    ID = ws.Rows(2).Find("ID", After:=ws.[K2]).Column
    ws.Range(ws.Cells(2, ID), ws.Cells.SpecialCells(11)).Copy r
    r.Offset(-1, 0).Value = ws.Name
    Set s = ws1.Cells(Rows.Count, 2).End(3)
    s.Offset(1, -1).Value = "Total"
    For i = 2 To ws1.Cells(s.Row, Columns.Count).End(xlToLeft).Column
    s.Offset(1, i - 2).Value = WorksheetFunction.Sum(Range(Cells(r.Row, i), Cells(s.Row, i)))
    Next
End If
Next
End Sub