To change the report layout of a PivotTable using VBA, you can use the `PivotTable` object’s `RowAxisLayout` property to set the layout you want. Excel provides different layout options for PivotTables, such as Compact, Outline, and Tabular.
Below is a simple guide along with a sample VBA code to demonstrate how you can change the report layout of a PivotTable:
Step-by-Step Guide
- Open the VBA Editor:
- Press `ALT` + `F11` to open the VBA Editor in Excel.
- Insert a Module:
- Go to `Insert` > `Module` to insert a new module where you can write your VBA code.
- Write the VBA Code:
- Use the provided sample code below, modifying it as needed for your specific Excel workbook and PivotTable.
Sample VBA Code
Sub ChangePivotTableLayout()
Dim ws As Worksheet
Dim pt As PivotTable
' Set the worksheet containing the PivotTable
Set ws = ThisWorkbook.Sheets("Sheet1") ' Change "Sheet1" to your sheet name
' Set the PivotTable by name
Set pt = ws.PivotTables("PivotTable1") ' Change "PivotTable1" to your PivotTable name
' Change the report layout
' Options:
' - xlCompactRow: Compact Form
' - xlTabularRow: Tabular Form
' - xlOutlineRow: Outline Form
pt.RowAxisLayout xlTabularRow
' Additional formatting options can be added here if needed
' For example, to show all items in tabular form with no subtotals:
' pt.ShowTableStyleRowStripes = False
' pt.SubtotalLocation xlAtTop
' Notify the user
MsgBox "PivotTable layout changed to Tabular Form"
End Sub
Explanation
- Worksheet and PivotTable Setup:
- Adjust the `ws` and `pt` variables to point to the correct worksheet and PivotTable in your workbook.
- RowAxisLayout Property:
- `xlCompactRow`: Sets the PivotTable to Compact Form.
- `xlTabularRow`: Sets the PivotTable to Tabular Form.
- `xlOutlineRow`: Sets the PivotTable to Outline Form.
- Customization:
- You can further customize the PivotTable by adding or modifying properties like `ShowTableStyleRowStripes`, `SubtotalLocation`, etc.
- Notification:
- A message box is displayed to notify you that the layout has been changed, which you can remove if you prefer no notifications.
Running the Code
- Run the Macro:
- Press `F5` or go to `Run` > `Run Sub/UserForm` in the VBA editor to execute the macro.
- Check the Results:
- Go back to Excel to see that the layout of your PivotTable has been changed according to the specified layout in the code.
Ensure your PivotTable names and worksheet names are accurately referenced in the code. You can find PivotTable names under the PivotTable Options in Excel.
Unlock Your Potential
Excel
Access
Power BI
