Not showing sub-totals in Pivot table using VBA

[g4cox]

Hi,

For some reason I am getting ‘run-time error 1004 unable to set the subtotals properly of the pivotfield class’ when I run the below code.

It appears to run fine upto 12 but on the 13^th it comes up with this error can anyone help?

Dim ptf As PivotField

With ActiveSheet.PivotTables("OOH") ' Switching off subtotals
        For Each ptf In .PivotFields
        ptf.Subtotals(1) = True
        ptf.Subtotals(1) = False
        Next ptf
    End With

This is the full code for pivot table creation.

    'Inserting Pivot table
    Dim ws, pivws As Worksheet
    Dim ptc As PivotCache
    Dim pt As PivotTable
    Dim ptf As PivotField
    Dim ptr As Range
    Dim lr As Long
    Set ws = Worksheets("Summarised Data")
    Set pivws = Worksheets("PivotData")
    ws.Select
    lr = ws.Cells(Rows.Count, 2).End(xlUp).Row
    Set ptr = Range("B1:M" & lr & "")
    pivws.Select


    Set ptc = ActiveWorkbook.PivotCaches.Create(SourceType:=xlDatabase, _
    SourceData:=ptr, Version:=xlPivotTableVersion14)


    Set pt = ptc.CreatePivotTable(TableDestination:=pivws.Cells(2, 2), _
    TableName:="OOH")
    
    With ActiveSheet.PivotTables("OOH").PivotFields("Month")
        .Orientation = xlRowField
        .Position = 1
    End With


    With ActiveSheet.PivotTables("OOH").PivotFields("Day")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("OOH").PivotFields("Location")
        .Orientation = xlColumnField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("OOH").PivotFields("Shift")
        .Orientation = xlRowField
        .Position = 3
    End With


    ActiveSheet.PivotTables("OOH").AddDataField ActiveSheet.PivotTables( _
        "OOH").PivotFields("No. of Base visits"), "Average number of base visits", xlSum


    ActiveSheet.PivotTables("OOH").AddDataField ActiveSheet.PivotTables( _
        "OOH").PivotFields("Time BV"), "Number of hours to see base visits", xlSum


    ActiveSheet.PivotTables("OOH").AddDataField ActiveSheet.PivotTables( _
        "OOH").PivotFields("Time HV"), "Number of hours to do home visits", xlSum


    With ActiveSheet.PivotTables("OOH").PivotFields("Average number of base visits")
        .NumberFormat = "#,##0.00"
    End With


    With ActiveSheet.PivotTables("OOH").PivotFields("Number of hours to see base visits")
        .NumberFormat = "#,##0.00"
    End With


    With ActiveSheet.PivotTables("OOH").PivotFields("Number of hours to do home visits")
        .NumberFormat = "#,##0.00"
    End With


        ActiveSheet.PivotTables("OOH").NullString = "0"


    With ActiveSheet.PivotTables("OOH").PivotFields("Shift")
        .PivotItems("Night Shift").Visible = False
'        .PivotItems("Not OOH").Visible = False
    End With
    ActiveSheet.PivotTables("OOH").PivotFields("Shift").PivotItems("Morning Shift") _
        .Position = 1
        
    With ActiveSheet.PivotTables("OOH") ' Switching off subtotals
        For Each ptf In .PivotFields
        ptf.Subtotals(1) = True
        ptf.Subtotals(1) = False
        Next ptf
    End With

Thanks

 

[g4cox]

My bad when run with all code (not just the pivot table creation) it works fine.