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


 
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