VBA Routine only pulling subset of records

[mhenk]

Hey all -

I'm using the following code to create a pivot table based off of three identically set up worksheets (different quarters of the same data).

Option Explicit

Sub MultipleSheetPTs()

Dim i As Long
Dim objPivotCache As PivotCache
Dim objRS As Object
Dim wbknew As Workbook
Dim wks As Worksheet
Dim arSQL() As String

With ActiveWorkbook
    ReDim arSQL(1 To .Worksheets.Count)
    For Each wks In .Worksheets
        i = i + 1
        arSQL(i) = "SELECT * From [" & wks.Name & "$]"
    Next wks

Set wks = Nothing
Set objRS = CreateObject("ADODB.Recordset")

objRS.Open Join$(arSQL, " UNION ALL "), Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", .FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)

objRS.Open Join$(arSQL, " UNION ALL "), Join$(Array("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=", .FullName, ";Extended Properties=""Excel 8.0;"""), vbNullString)

End With

Set wbknew = Workbooks.Add(template:=xlWBATWorksheet)

With wbknew
    Set objPivotCache = .PivotCaches.Add(xlExternal)
    Set objPivotCache.Recordset = objRS
    Set objRS = Nothing

    With .Worksheets(1)
        objPivotCache.CreatePivotTable TableDestination:=.Range("A1")
    Set objPivotCache = Nothing
    Range("A1").Select
End With
End With
Set wbknew = Nothing
End Sub

The code works great, except for the small fact that it's only pulling the first 12,218 records of each worksheet in the source file.

Is this a vba limitation? A pivot table limitation?

Let me know if you can lend me a hand. Thanks much.