PivotCaches.create with SourceData exceeding 65535 rows RRS feed

  • Question

  • Can anybody please explain why I cannot use range with more than 65535 row with Source data when using PivotCaches.Create method?

    I am using Excel 2013, also using Version:=xlPivotTableVersion14 (which corresponds to xl 2010 if I am not mistaken).

    I read I can use name range to solve the issue, but just want to know any reference why current code is not working? Same code works if I just set rng with less than 65535 rows.

    Set ws = Worksheets("mydata")
    Set rng = ws.Range("A1:C66000")
    'Set rng = ws.Range("A1:C65000")  THIS WORKS
    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:=DataRange, Version:=xlPivotTableVersion14).CreatePivotTable _
            TableDestination:=Worksheets("pvot").Range("A1"), TableName:="mypv", DefaultVersion:=xlPivotTableVersion14

    ERROR: Error 13, Type Mismatch

    Wednesday, November 19, 2014 7:12 AM


  • Hi AbdFahim,

    Please take a look at this thread which is talking about exactly the same problem with yours, the OP in this thread has provided a solution:

    PivotCache "Type mismatch" error when >65,536 rows

    Running the recorder got this   thing back on track. I'd run it earlier and didn't catch the difference in   the SourceData:= section. My code is using a Range reference while the   recorder uses the R1C1 reference. Using a Range, even with the v14 entries,   still caused a Type Mismatch. However switching to R1C1 gets it working.

    So, this example fails with a   Type Mismatch error:
          Set PRange = WSD.Cells(1, 1).Resize(finalRow, finalCol)
          ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase,   SourceData:= _
              PRange,   Version:=xlPivotTableVersion14).CreatePivotTable _
                TableDestination:="Table!R1C1", TableName:="PivotTable2",   DefaultVersion _

    This example works!:
          ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase,   SourceData:= _
              "Detail!R1C1:R"   & finalRow & "C" & finalCol).CreatePivotTable   _
                TableDestination:="Table!R1C1", TableName:="PivotTable2"

    Thank you for the help. It   forced a closer look at the code differences and uncovered the problem.

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Thursday, November 20, 2014 9:59 AM