Select and Format: PivotTable > PivotField > Subtotals

[mjseim]

I am having a most difficult time formatting my PivotTable; well, the subtotals of my PivotTable that is. The rest of my formatting through code is working perfectly. I've pasted the code I'm currently using so that you can get an idea of what I'm trying to do.

As you can see from the code below, I can select and format all my PivotFields just fine. It's the subtotals that I can't seem to select or format without selecting. Normally to figure this stuff out I'd just record a macro of me formatting this data... however, in the case of Subtotals and Excel 2003, you can't select subtotal rows.

Thanks.

Sub RefreshReport()
    
    '------------------------------------------------------------------------------------------------------------------
    ' Prepare Macro
        On Error GoTo ErrorMessage
        Call UnprotectAll
        Application.ScreenUpdating = False
        Application.Calculation = xlCalculationManual
        Application.StatusBar = "Updating the report for viewing..."
    
    '------------------------------------------------------------------------------------------------------------------
    ' Establish Variables
        Dim strPivotTable As String
        strPivotTable = "ptCashFlowConsolidated"

    '------------------------------------------------------------------------------------------------------------------
    'Refresh PivotTable
        ActiveSheet.PivotTables(strPivotTable).PivotCache.Refresh

    '------------------------------------------------------------------------------------------------------------------
    'Format PivotTable:  Region
        ActiveSheet.PivotTables(strPivotTable).PivotSelect "Reg.[All]", xlDataAndLabel + xlFirstRow, True
        With Selection
            .Interior.ColorIndex = 36      'centex dark grey
            .Font.ColorIndex = 2           'white
            .Font.Bold = True
            .Font.Size = 10
            .Borders(xlEdgeBottom).LineStyle = xlNone
            .Borders(xlInsideHorizontal).LineStyle = xlNone
        End With

    '------------------------------------------------------------------------------------------------------------------
    'Format PivotTable:  Division
        ActiveSheet.PivotTables(strPivotTable).PivotSelect "Div.[All]", xlDataAndLabel + xlFirstRow, True
        With Selection
            .Interior.ColorIndex = 35      'centex grey
            .Font.ColorIndex = 0           'automatic
            .Font.Bold = True
            .Borders(xlEdgeBottom).LineStyle = xlNone
            .Borders(xlInsideHorizontal).LineStyle = xlNone
        End With

    '------------------------------------------------------------------------------------------------------------------
    'Format PivotTable:  Cost Type
        ActiveSheet.PivotTables(strPivotTable).PivotSelect "Cost Type[All]", xlDataAndLabel + xlFirstRow, True
        With Selection
            .Interior.ColorIndex = xlNone  'blank
            .Font.ColorIndex = 0           'black
            .Font.Bold = False
            .Borders(xlEdgeTop).LineStyle = xlNone
            .Borders(xlEdgeBottom).LineStyle = xlDot
            .Borders(xlInsideHorizontal).LineStyle = xlDot
        End With
        
'------------------------------------------------------------------------------------------------------------------
'TESTING SECTION:  Trying to format Subtotals
'
'        ActiveSheet.PivotTables(strPivotTable).PivotFields("Cost Type").Subtotal.Font.Bold = True
'
'        ActiveSheet.PivotTables(strPivotTable).PivotSelect "Discretionary Sum", xlDataAndLabel + xlFirstRow, True
'        With Selection
'            .Interior.ColorIndex = xlNone  'blank
'            .Font.ColorIndex = 0           'black
'            .Font.Bold = False
'            .Borders(xlEdgeTop).LineStyle = xlNone
'            .Borders(xlEdgeBottom).LineStyle = xlDot
'            .Borders(xlInsideHorizontal).LineStyle = xlDot
'        End With
        
        
    '------------------------------------------------------------------------------------------------------------------
    'Format PivotTable:  Grand Total Row
        ActiveSheet.PivotTables(strPivotTable).PivotSelect "'Column Grand Total'", xlDataAndLabel + xlFirstRow, True
        With Selection
            .Font.Size = 10
        End With

    '------------------------------------------------------------------------------------------------------------------
    'Format PivotTable:  Page Fields
        With Range("A5:B5")                                                 '<------ HARD REFERENCE
            .Font.ColorIndex = 2
            .Borders(xlEdgeLeft).LineStyle = xlContinuous
            .Borders(xlEdgeTop).LineStyle = xlNone
            .Borders(xlEdgeBottom).LineStyle = xlNone
            .Borders(xlInsideVertical).LineStyle = xlNone
            .Borders(xlInsideHorizontal).LineStyle = xlNone
        End With

    '------------------------------------------------------------------------------------------------------------------
    'Adjust column widths
        Cells.EntireColumn.AutoFit
        Range("A:B").ColumnWidth = 6                                        '<------ HARD REFERENCE

    '------------------------------------------------------------------------------------------------------------------
    ' Move to top cell
        Range("ReportHeaderPT").EntireRow.Hidden = True
        Range("A1").Select                                                  '<------ HARD REFERENCE
        

ErrorExit:
    If Protected = True Then Call ProtectAll
    
    Application.Calculation = xlCalculationAutomatic
    Application.StatusBar = False
    Application.ScreenUpdating = True
    
    Exit Sub

ErrorMessage:
    prompt = MsgBox("The following error has arisen:" & _
        vbNewLine & "    Number:" & vbTab & Err.Number & _
        vbNewLine & "    Description:" & vbTab & Err.Description & _
        vbNewLine & "    Project Source:" & vbTab & Err.Source & _
        vbNewLine & "    Status Bar:" & vbTab & Application.StatusBar & _
        vbNewLine & vbNewLine & vbNewLine, _
        vbCritical + vbOKOnly, "Macro Has Failed")
        
    GoTo ErrorExit
    
End Sub

 

[mjseim]

I GOT IT!!! Turns out, I just couldn't select the SubTotal regions with the Autoformat design that I had originally selected. So, if ever someone looks at this with the same problem, see the change int he code below:

'------------------------------------------------------------------------------------------------------------------
'TESTING SECTION:  Trying to format Subtotals
'
'        ActiveSheet.PivotTables(strPivotTable).PivotFields("Cost Type").Subtotal.Font.Bold = True
'
'        ActiveSheet.PivotTables(strPivotTable).PivotSelect "Discretionary Sum", xlDataAndLabel + xlFirstRow, True
'        With Selection
'            .Interior.ColorIndex = xlNone  'blank
'            .Font.ColorIndex = 0           'black
'            .Font.Bold = False
'            .Borders(xlEdgeTop).LineStyle = xlNone
'            .Borders(xlEdgeBottom).LineStyle = xlDot
'            .Borders(xlInsideHorizontal).LineStyle = xlDot
'        End With
        ActiveSheet.PivotTables("ptCashFlowConsolidated").PivotSelect "'Cost Type'[All;Sum]", xlDataAndLabel, True
        With Selection
            .Interior.ColorIndex = 35      'centex grey
            .Font.ColorIndex = 0           'automatic
            .Font.Bold = True
        End With

 

[mikeopolo]

Thank you for sharing your solution. I've been looking everywhere for the pivotselect syntax, and finally found this post.

fwiw:

vba: .PivotSelect "'Cost Type'[All;Sum]", xlDataAndLabel, True

foxpro: .PivotSelect("'Cost Type'[All;Sum]", 0,.t.)