Search through workbooks for string, copy to current book

[mrshl9898]

Hi all,

I have hundreds of workbooks to search through, I have to search for cells containing "First Class Lounge" (may be "First class lounge - Sydney" or a few other variants), then copy the rows to my current book.

This is what I have so far:

Sub RunCodeOnAllFiles()
    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
        .LookIn = "N:\Customers\Active\Moet Hennessy\Weekly Settlement\71"
        .FileType = msoFileTypeExcelWorkbooks
        '.Filename = "Book*.xls"
         
        If .Execute > 0 Then 'Workbooks in folder
            For lCount = 1 To .FoundFiles.Count 'Loop through all.
                Set wbResults = Workbooks.Open(Filename:=.FoundFiles(lCount), UpdateLinks:=0)
                 

                 Sheets("Weekly Settlement").Select
                 
                 
                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

I'm trying to open both .xls and .csv files for this (currently only looking for .xls and unsure how to change), though in both cases the tab name will be "Weekly Settlement".

Many thanks for any pointers!!