ActiveCell.FormulaArray = "=ВПР(МАКС(ЕСЛИ(AAA!$A$4:$A$150<'RRR'!P8;AAA!$A$4:$A$150));AAA!$A$4:$B$150;2;0)"

Sub aaa()
Dim app As Excel.Application
Dim Book As Excel.Workbook
Dim wb1 As Workbook, sh1 As Worksheet

Set app = CreateObject("Excel.Application")
Set Book = app.Workbooks.Add
Set wb1 = Workbooks(ThisWorkbook.Name)

FF = "111"
For Each sh1 In wb1.Worksheets
    if .... Then
        if ... Then
            Book.Worksheets.Add after:=Book.Worksheets(Book.Worksheets.Count)
            Book.Worksheets(Book.Worksheets.Count).Name = sh1.Name
            sh1.ListObjects(1).Range.Copy _
            Book.Worksheets(Book.Worksheets.Count).Range("A1")
        End If
    End If
Next sh1

Book.SaveAs Filename:=FF, FileFormat:=xlExcel8
Book.Close

End Sub

Private Sub ComboBox1_DropButtonClick()
Dim wb As Workbook, s, i
Dim arr()

Set wb = Workbooks(ThisWorkbook.Name)
s = wb.Worksheets.Count

ReDim arr(1 To s)
For i = 1 To s
If wb.Worksheets(i).Name <> "Лист1" Then
arr(i) = wb.Worksheets(i).Name
End If
Next i

ComboBox1.List = arr

End Sub

if строка существует Then
msgbox номер строки
Else
msgbox строки нет
end if

Sub Кнопка2_Щелчок()

    Dim sPath As String, sFile As String, sShName As String
    sPath = "XXX" '"
    sFile = "YYY.xls" '"
    sShName = "Таблица1" '"
    Application.DisplayAlerts = 0
       
    With Sheets("Лист1").Range("A1:O1000")
        .Formula = "='" & sPath & "[" & sFile & "]" & sShName & "'!" & "A1" '"
        .Value = .Value
    
    
    For Each i In Range(Cells(1, 2), Cells(1000, 2))
    If i.Value = 333 Then k = k + 1
    Next i

    L = k + 2
    Rows("" & L & ":1000").Select
    Selection.Delete Shift:=xlUp
    Application.DisplayAlerts = 1
    End With
    
   
End Sub

 With Worksheets("Лист3").QueryTables.Add(Connection:=XXX, Destination:=Worksheets("Лист3").Range("A1"))
 .CommandText = SQL
.Name = "YYY"
.Refresh BackgroundQuery:=False
End With

With Worksheets("Лист3").QueryTables.Add
E55:
If Worksheets("Лист3").QueryTables.Adding Then
GoTo E55
Else
MsgBox "11111"
End If
End With

With Worksheets("Лист5").QueryTables.Add(Connection:=..., Destination:=Range("A1"))
      .CommandText = "select...."
        .Name = "ХХХХ"
        .Refresh BackgroundQuery:=False
    End With

ActiveWorkbook.RefreshAll

ActiveWorkbook.Sheets("Лист1").ListObject.QueryTable.Refresh BackgroundQuery:=False