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.
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)
foundfile = Dir
Set ws2 = Nothing
Set wb = Nothing