'кнопка удаления существующего в книге листа
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)

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