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:
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!!
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:
Code:
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!!