'кнопка удаления существующего в книге листа
Private Sub CommandButton1_Click()
Call SheetDeleter("Main")
End Sub

'кнопка создания несуществующего в книге листа
Private Sub CommandButton2_Click()
Call SheetCreator("Main")
End Sub

'Процедура удаления листа
Sub SheetDeleter(ByVal pSheetName As String)

If ExistSheetWithThatName(pSheetName) Then
    Application.DisplayAlerts = False
    ThisWorkbook.Sheets(pSheetName).Delete
    Application.DisplayAlerts = True
End If

End Sub

'Процедура создания листа
Sub SheetCreator(ByVal pSheetName As String)

If Not ExistSheetWithThatName(pSheetName) Then
    ThisWorkbook.Sheets.Add.Name = pSheetName
End If

End Sub

Dim con As New ADODB.Connection
Dim rs As New ADODB.Recordset

With con
    .Provider = "..."
    .ConnectionString = "..."
    .Open
End With

QueryText= "select * from table"

rs.Open QueryText, con

Dim x As Variant
x = rs.GetRows      ' <- проблемная часть кода

смещ($A$1;1;0;счетз($A:$A)-1;1)

двссыл($G$1)

range("A1").currentregion.copy, range(...).select, range("A1").pastespecial ...

=ЕСЛИОШИБКА(ЕСЛИОШИБКА(ВПР(A2;ЕСЛИ((I:I="ПУШ")*(G:G=A2);G:H);2;0);ВПР(A2;ЕСЛИ((I:I<>"Отгрузка")*(G:G=A2);G:H);2;0));ВПР(A2;ЕСЛИ((I:I="Отгрузка")*(G:G=A2);G:H);2;0))

=ВПР(A2;ЕСЛИ(($J$4:$J$10="ПУШ")*($H$4:$H$10=A2);$H$4:$I$10;ЕСЛИ(($H$4:$H$10=A2)*($J$4:$J$10="Отгрузка");$H$4:$I$10));2;0)

Function VLOOKUP2(Table As Variant, SearchColumnNum As Long, SearchValue As Variant, _
                  N As Long, ResultColumnNum As Long)
    Dim i As Long, iCount As Long
    Select Case TypeName(Table)
    Case "Range"
        For i = 1 To Table.Rows.Count
            If Table.Cells(i, SearchColumnNum) = SearchValue Then
                iCount = iCount + 1
            End If
            If iCount = N Then
                VLOOKUP2 = Table.Cells(i, ResultColumnNum)
                Exit For
            End If
        Next i
    Case "Variant()"
        For i = 1 To UBound(Table)
            If Table(i, SearchColumnNum) = SearchValue Then iCount = iCount + 1
            If iCount = N Then
                VLOOKUP2 = Table(i, ResultColumnNum)
                Exit For
            End If
        Next i
    End Select
End Function