Excel数据透视表宏：如何将横向筛选器调整为垂直排列？

Hey there! Sounds like your recorded pivot table macro is putting those 5 filters side-by-side instead of stacking them vertically—super frustrating, right? Let’s get this sorted out quickly.

Why This Happens #

When you record a macro for pivot tables, Excel uses default layout settings: by default, page fields (your filters) are set to arrange horizontally first (xlOverThenDown) before wrapping to a new row. That’s why your filters are showing up in a horizontal line instead of a vertical stack.

Quick Manual Fix (Then Re-Record If Needed) #

If you just need to fix the existing pivot table first:

• Click anywhere on your pivot table to bring up the PivotTable Fields pane.
• At the bottom of the pane, click the small gear icon labeled Field Section and Area Section Layout.
• Select Show in Vertical Order (or "垂直并排" for Chinese Excel). Your filters will stack vertically immediately.

If you want your macro to handle this automatically next time, re-record the macro after setting this layout—Excel will capture the vertical layout settings in the new macro code.

Permanent Fix: Modify Your Existing VBA Code #

To tweak your current macro so it always creates vertically arranged filters, add a few lines to control the page field layout. Here’s how to adjust your code:

First, we’ll clean up your code by assigning the pivot table to a variable (makes it easier to modify):

Range("Table1[[#Headers],[Installation]]").Select
' Create a new sheet and assign it to a variable
Dim newSheet As Worksheet
Set newSheet = Sheets.Add

' Create the pivot table and store it in a variable
Dim myPivot As PivotTable
Set myPivot = ActiveWorkbook.PivotCaches.Create( _
    SourceType:=xlDatabase, _
    SourceData:="Table1", _
    Version:=6 _
).CreatePivotTable( _
    TableDestination:=newSheet.Range("A1"), _
    TableName:="MyVerticalPivot" _
)

' This is the key part: set filters to arrange vertically
myPivot.PageFieldOrder = xlDownThenOver
' Set to 1 so each column holds only 1 filter (forces a full vertical stack)
myPivot.PageFieldWrapCount = 1

' Now add your 5 filter fields (example with Installation)
myPivot.PivotFields("Installation").Orientation = xlPageField
' Repeat this line for your other 4 filter fields, replacing the field name each time

What These Lines Do:

• PageFieldOrder = xlDownThenOver: Tells Excel to place filters vertically first (down the column) before moving to the next column.
• PageFieldWrapCount = 1: Ensures only one filter is placed per column, so all 5 filters stack neatly in a single vertical list.

Verify the Fix #

Run your modified macro, and your pivot table filters should now appear stacked vertically instead of side-by-side, matching your "correct format" example.

内容的提问来源于stack exchange，提问作者Steph P