Run time Error 13 VBA Pivot creation

[rdw72777]

Howdy all,

Encountered this issue today but concerned more as I delve deeper. I know the issue of getting type mismatch errors when creating pivots through code is common. In my code below, I'm trying to create a pivot and set it as a varible (Set PivotTbl) and am getting an error.

I know the most common error is to that the column headers in the source data range are invalid; as such, I have the code select and if I step through to the point where it selects the range, I can create the pivot manually with no error. So I'm wodnering if it has something to do with the Set PivotTbl, but this is code I use frequently so I'm perplexed.

Is there a limit on the amount of data to be passed through when creating a pivot in VBA? My source data has over 100,000 rows but it seems like that shouldn't be the issue. Any other thoughts?

'=================================================================================================
'=============START:  Create Pivot=================================================================

'   Make pivot based off all data on the myNormalizedData tab
'   variables from FormSheet tab will be passed through as filters
'   Summarize the data
'   This will be done by creating a new ws as a pivot table
'

    Dim PivotTbl As PivotTable
    Dim destws As Worksheet
    Dim DestRng As Range
    
    'Insert new sheet and name it "mysheet", this will be the newly created report
    Sheets.Add After:=Sheets(Sheets.Count)
    ActiveSheet.Name = "myPivot"
    Set PivotSheet = wkb.Worksheets("myPivot")
    
    Set DestRng = PivotSheet.Cells(3, 1)
    
    'All of these selects work fine, so the ranges are being recognized
    NormalDataSheet.Select
    NormalDataSheet.Cells(1, 1).CurrentRegion.Select
    PivotSheet.Select
    Range("A3").Select
    
'This doesn't work (Run time error 13/Type Mismatch     
    Set PivotTbl = ActiveWorkbook.PivotCaches.Create(xlDatabase, NormalDataSheet.Cells(3, 1).CurrentRegion, _
        xlPivotTableVersion12).CreatePivotTable("myPivot!R3C1", "aaa", True, xlPivotTableVersion12)

    'This does work
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "myNormalizedData!R1C1:R102481C8", Version:=xlPivotTableVersion12). _
        CreatePivotTable TableDestination:="myPivot!R3C1", TableName:="PivotTable1" _
        , DefaultVersion:=xlPivotTableVersion12