Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)
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
Join the community of 500,000 technology professionals and ask your questions.