Macro to pull multiple files from a directory

Question:
Hello

Here's the situation.

I have a directory file that has multiple spreadsheets. Each spreadsheet has 3 worksheets within it.
On the second worksheet title "SST" there is data from columns A-I, and a varying amount of rows.

I need a macro that will extract the data from the SST worksheets of all excel files within the directory, and then populate that into a single worksheet.

attached is some code that doesn't work, but is what I'm working with.

thanks
Brandon

Sub Sample()
    Dim wb As Workbook
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim i As Long, LastRowWs1 As Long, LastRowWs2 As Long
    Dim foundfile As String, pathoffiles As String
    
    Set ws1 = ActiveWorkbook.Sheets("Sheet1")
    
    '~~> Change path of the directory here
    pathoffiles = "C:\Temp\"
    
    foundfile = Dir(pathoffiles & "*.xls") '<~~~  .xlsx of 2007/2010
    
    Do While Len(foundfile) <> 0
        
        LastRowWs1 = ws1.Range("A" & Rows.Count).End(xlUp).Row
        
        Set wb = Workbooks.Open(pathoffiles & foundfile)
        Set ws2 = wb.Sheets("SST")
        
        LastRowWs2 = ws2.Range("A" & Rows.Count).End(xlUp).Row
        
        ws2.Range("A1:A" & LastRowWs2).Copy ws1.Range("A" & LastRowWs1)
        
        wb.Close savechanges:=False
        
        foundfile = Dir
    Loop

    Set ws2 = Nothing
    Set wb = Nothing
End Sub

Select allOpen in new window