Why won't pivot work?

[theta]

Hi All. I have a large range. When I select A1:CC130000 I can create a pivot manually no problems.

I can record a macro to produce the pivot. With a smaller range it works fine, but when using the above range it fails. PivotCache failed error

This is very frustrating, just used macro recorder to produce a dirty pivot in its basic form and it still fails

Ideas?

 

[RoryA]

What does the macro code look like and where does it fail?

 

[theta]

Hi Rorya, I will upload the code in a moment, just testing now.

I have a dirty method that works. Then I have a seperate method using Set with PivotCache and PivotTable objects. This method fails. Will upload them both for comparison along with error description

...

 

[theta]

Managed to narrow it down (but still not making sense)

If I use a fixed range for the source then the macro will run.

If I use a range method, calling nLastRow and nLastCol then it fails?

FAILS

    ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        Range(Sheets("HPMN").Cells(1, 1), Sheets("HPMN").Cells(nLastRow, nLastCol)), Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="HPMN!R1C1", TableName:="HPMN Codes", DefaultVersion:= _
        xlPivotTableVersion12
    Sheets("HPMN").Select
    Cells(1, 1).Select
    
    ActiveSheet.PivotTables("HPMN Codes").AddDataField ActiveSheet.PivotTables( _
        "HPMN Codes").PivotFields("TAPSeqNo"), "Min of TAPSeqNo", xlMin
    
    ActiveSheet.PivotTables("HPMN Codes").AddDataField ActiveSheet.PivotTables( _
        "HPMN Codes").PivotFields("TAPSeqNo"), "Max of TAPSeqNo", xlMax
        
    ActiveSheet.PivotTables("HPMN Codes").AddDataField ActiveSheet.PivotTables( _
        "HPMN Codes").PivotFields("TotalNetCharge"), "Sum of TotalNetCharge", _
        xlSum
        
    ActiveSheet.PivotTables("HPMN Codes").AddDataField ActiveSheet.PivotTables( _
        "HPMN Codes").PivotFields("TotalTax"), "Sum of TotalTax", xlSum
        
    With ActiveSheet.PivotTables("HPMN Codes").PivotFields("HPMN")
        .Orientation = xlRowField
        .Position = 1
    End With

WORKS

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= _
        "XML Data!R1C1:R127385C81", Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="HPMN!R1C1", TableName:="HPMN Codes", DefaultVersion:= _
        xlPivotTableVersion12
    Sheets("HPMN").Select
    Cells(1, 1).Select
    
    ActiveSheet.PivotTables("HPMN Codes").AddDataField ActiveSheet.PivotTables( _
        "HPMN Codes").PivotFields("TAPSeqNo"), "Min of TAPSeqNo", xlMin
    
    ActiveSheet.PivotTables("HPMN Codes").AddDataField ActiveSheet.PivotTables( _
        "HPMN Codes").PivotFields("TAPSeqNo"), "Max of TAPSeqNo", xlMax
        
    ActiveSheet.PivotTables("HPMN Codes").AddDataField ActiveSheet.PivotTables( _
        "HPMN Codes").PivotFields("TotalNetCharge"), "Sum of TotalNetCharge", _
        xlSum
        
    ActiveSheet.PivotTables("HPMN Codes").AddDataField ActiveSheet.PivotTables( _
        "HPMN Codes").PivotFields("TotalTax"), "Sum of TotalTax", xlSum
        
    With ActiveSheet.PivotTables("HPMN Codes").PivotFields("HPMN")
        .Orientation = xlRowField
        .Position = 1
    End With

 

[theta]

This part works : "XML Data!R1C1:R127385C81",

But when using a Cells() range, with nLastRow nLastCol it fails.

 

[RoryA]

Try using:

ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, SourceData:= "'HPMN'!R1C1:R" & nLastRow & "C" & nLastCol, Version:=xlPivotTableVersion12).CreatePivotTable _
        TableDestination:="HPMN!R1C1", TableName:="HPMN Codes", DefaultVersion:= _
        xlPivotTableVersion12

 

[theta]

Hmmm seems to work. Why is this happening, very strange

Could I not create a variable before the pivot create, called e.g. RangeToPivot, then call that directly in the pivotcreate method. Just to make it cleaner for me if I need to change in the future etc?

 

[RoryA]

You could, but I don't recommend it. In my experience pivot tables work best if you pass them an R1C1 format address string rather than a range object, especially if you are using lots of data.

 

[theta]

Perfect. So this is a known issue? Simple changing from range to R1C1 has fixed the problem

Guessing that a large range is very intensive, so the extra requirement to calculate a range instead of an R1C1 reference causes the failure?

?

 

[RoryA]

No idea why - just one of the many quirks of Excel.