Custom Formatting of PivotTable - by Its Structure

     

     

    Sub Select_PivotTable_Step_By_Step()
    InputSH
    Define_PivotTable
    PV.PivotSelect "", xlDataAndLabel, True
    PV.PivotSelect "", xlDataOnly, True
    PV.PivotSelect "", xlLabelOnly, True
    PV.PivotSelect "Location", xlDataAndLabel, True
    PV.PivotSelect "Location", xlDataOnly, True
    PV.PivotSelect "Location", xlLabelOnly, True
    PV.PivotSelect "Zone", xlLabelOnly, True
    PV.PivotSelect "Zone", xlButton, True
    PV.PivotSelect "Item", xlDataAndLabel, True
    PV.PivotSelect "Item", xlLabelOnly, True
    PV.PivotSelect "Item", xlDataOnly, True
    PV.PivotSelect "Apple", xlDataAndLabel, True
    PV.PivotSelect "Apple", xlDataOnly, True
    PV.PivotSelect "Apple", xlLabelOnly, True
    PV.PivotSelect "Grapes", xlFirstRow, True
    PV.PivotSelect "Apple", xlOrigin, True
    PV.PivotSelect "Item", xlBlanks, True
    PV.PivotSelect "Banglore", xlDataAndLabel, True
    PV.PivotSelect "Banglore", xlDataOnly, True
    PV.PivotSelect "Banglore", xlLabelOnly, True
    PV.PivotSelect "Pune", xlDataAndLabel, True
    PV.PivotSelect "'Column Grand Total'", xlDataAndLabel + xlFirstRow, True
    PV.PivotSelect "'Column Grand Total'", xlDataOnly, True
    PV.PivotSelect "'Column Grand Total'", xlLabelOnly, True
    PV.PivotSelect "'Row Grand Total'", xlDataAndLabel, True
    PV.PivotSelect "'Row Grand Total'", xlDataOnly, True
    PV.PivotSelect "'Row Grand Total'", xlLabelOnly, True
    End Sub

     

     

    Format the PivotTable:

     

    Sub Format_PivotTable()

    InputSH
    Define_PivotTable

    PV.PivotSelect "", xlDataAndLabel, True
    With Selection
    .HorizontalAlignment = xlCenter
    .VerticalAlignment = xlCenter
    .Font.Name = "Century"
    .Font.Size = 13
    .Columns.AutoFit
    End With

    PV.PivotSelect "", xlLabelOnly, True
    With Selection
    .Interior.ColorIndex = 9
    .Font.ColorIndex = 2
    End With

    PV.PivotSelect "Location", xlLabelOnly, True
    With Selection
    .Font.ColorIndex = 9
    .HorizontalAlignment = xlLeft
    .Font.Bold = True
    .Interior.ColorIndex = 2
    End With

    PV.PivotSelect "Location", xlDataOnly, True
    With Selection
    .Font.ColorIndex = 5
    End With

    PV.PivotSelect "Zone", xlLabelOnly, True
    With Selection
    .Interior.ColorIndex = 5
    .Font.ColorIndex = 2
    End With

    PV.PivotSelect "Zone", xlButton, True
    With Selection
    .Interior.ColorIndex = 5
    .Font.ColorIndex = 2
    End With

    PV.PivotSelect "'Row Grand Total'", xlDataOnly
    With Selection
    '.Interior.ColorIndex = 9
    .Font.ColorIndex = 9
    .Font.Bold = True
    End With

    PV.PivotSelect "'Column Grand Total'", xlDataAndLabel, True
    With Selection
    .Interior.ColorIndex = 9
    .Font.ColorIndex = 2
    End With

    PV.PivotSelect "Item", xlLabelOnly, True
    With Selection
    '.Font.ColorIndex = 2
    .Interior.ColorIndex = 10
    End With

    PV.PivotSelect "Banana", xlLabelOnly, True
    With Selection
    .Interior.ColorIndex = 9
    End With

    End Sub

     

    Download The Workbook

     

     

Hi Everyone,
Welcome to VBA Macros Tutorial. This Tutorial provides comprehensive knowledge on VBA Macros to the user. Hope it enhances your coding skills. If you like this web page kindly Share this to your near and dear. I Prepared this content as per my level of knowledge, if any changes required kindly bring to my notice. Thanks for visiting this web page

Thanks,
Pavan Kumar Gundlapalli