Reply to thread

Message: [QUOTE="VBA Noob, post: 8436544"] Hi, I've got 20 spreadsheets called Week 1 to Week 20 each has two sheets called Wk 42 T and Week 42 R. They are stored in a shared area on the K Drive. I want to copy all the data from Column M in both sheets (around 180 to 200 lines) and then transfer the data to a workbook called "summary" with two sheets called "Totals T" & "Total R". I've was thinking of using the below code to open the workbooks but is this the best way??. If it is the best way I need help with selecting the data from the Workbooks called Week 1 to Week 20 in the K drive and then looping through each workbook and the two worksheets and pasting that data in Column A in "Totals T" (all info from Wk 42 T) & "Total R" (all info from Wk 42 R) in the workbook called "Summary" Any help greatly appreciated Code: -------------------- Sub RunCodeOnAllXLSFiles() Dim lCount As Long Dim wbResults As Workbook Dim wbCodeBook As Workbook Application.ScreenUpdating = False Application.DisplayAlerts = False Application.EnableEvents = False On Error Resume Next Set wbCodeBook = ThisWorkbook With Application.FileSearch .NewSearch 'Change path to suit .LookIn = "C:\MyDocuments\TestResults" .FileType = msoFileTypeExcelWorkbooks '.Filename = "Book*.xls" If .Execute > 0 Then 'Workbooks in folder For lCount = 1 To .FoundFiles.Count 'Loop through all. 'Open Workbook x and Set a Workbook variable to it Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0) 'CODE HERE wbResults.Close SaveChanges:=True Next lCount End If End With On Error Goto 0 Application.ScreenUpdating = True Application.DisplayAlerts = True Application.EnableEvents = True End Sub -------------------- Thanks VBA Noob [/QUOTE]

Verification