Add data from multiple workbooks that are open and save it in a new workbook

Dear All,

I am new to this forum and Excel VBA probramming. What i am trying to achieve is add data from multiple open workbooks and save it in a new workbook. I am trying to acheive this with the following code but i get stuck when the code reaches "For Each Wk In Wks
Sheets("Self Assessment").Activate". Please help


Code

Sub AddSaveAsNewWorkbook()
    Dim Wk As Workbook
    Dim Wks As Workbooks
    Dim x As Integer
    x = 0
    Set Wks = Application.Workbooks
    Set Wk = Workbooks.Add
    Application.DisplayAlerts = False
    Wk.SaveAs Filename:="D:\Work\Learning\Excel VBA\Consolidated.xls"   ' Creates new Workbook
    Windows("Self Assessment Tool for Corp Licensees 2.xlsm").Activate  ' This is an existing file with data
    ' The following code is to copy and paste all the headings and format into the new workbook
Sheets("Self Assessment").Activate
Range("D6:H258").ClearContents
Range("A1:I392").Select
    Selection.Copy
    Windows("Consolidated.xls").Activate
    ActiveSheet.Paste
    Columns("D:H").Select
    Selection.ColumnWidth = 4
    Columns("B").ColumnWidth = 36
    Columns("C").ColumnWidth = 68
    Rows(3).RowHeight = 110
    Rows(1).RowHeight = 31
    Rows("261:392").RowHeight = 16.5
    ActiveWindow.SmallScroll Down:=-12
    ' The following code is trying to calculate the sum of Value of all D6 cells from all open workbooks
    ' and save it in the new workbook.
    
    For Each Wk In Wks
    Sheets("Self Assessment").Activate
    x = x + Range("D6").Value
    Next Wk
    
    Windows("Consolidated.xls").Activate
    Sheets("Sheet1").Activate
    Range("D6").Value = x
    Range("D6").Copy
    Range("D6:H258").Paste
End Sub

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here