add data to pivot table cache

Public Sub ConsolidateAndPivot()
    Dim rngCell As Range, strRange As String, rngData As Range
    Dim pvc As PivotCache, pvt As PivotTable
    Dim arrSheets As Variant, wks As Worksheet
    Dim recData As ADOR.Recordset
    
    Set recData = New ADOR.Recordset
    arrSheets = Array("Sheet1", "Sheet2", "Sheet3")
    strRange = "A:E"
    Set pvt = Sheets(1).PivotTables(1)
    
    For Each wks In Sheets(arrSheets)
        Set rngData = Intersect(wks.Range(strRange), wks.UsedRange)
        If wks.Name = arrSheets(0) Then
            For Each rngCell In rngData.Resize(1)
                With recData
                    With .Fields
                        Select Case TypeName(rngCell.Offset(1).Value)
                            Case "String"
                                .Append rngCell.Text, adVarChar, 255
                            Case "Date"
                                .Append rngCell.Text, adDate
                            Case "Double", "Currency"
                                .Append rngCell.Text, adDouble
                        End Select
                    End With
                End With
            Next rngCell
        End If
        If recData.State = 0 Then recData.Open
        Set rngData = Intersect(rngData.Offset(1), wks.UsedRange)
        For Each rngCell In rngData
            With recData
                If rngData(0, rngCell.Column).Text = .Fields(0).Name Then .AddNew
                .Fields(rngData(0, rngCell.Column).Text).Value = rngCell.Value
                .Update
            End With
        Next rngCell
    Next wks
    
    With Sheets.Add(Sheets(1))
        Set pvc = ThisWorkbook.PivotCaches.Add(SourceType:=xlExternal)
        Set pvc.Recordset = recData
        With pvc.CreatePivotTable(TableDestination:=.Range("A1"))
            pvt.CacheIndex = .CacheIndex
        End With
        Application.DisplayAlerts = False
            .Delete
        Application.DisplayAlerts = True
    End With
    
    recData.Close
    Set recData = Nothing
End Sub

Dim recData As ADOR.Recordset