VBA生成数据透视表仅显示单个行字段值，如何无需手动刷新修复？

这个问题核心是透视缓存未同步完整数据源，或字段设置后未触发全量更新，以下是针对性修改方案：

关键调整点 #

• 创建透视缓存后立即刷新，确保加载完整数据源
• 设置字段前关闭透视表手动更新模式，让配置实时生效
• 调整布局设置时机，确保在所有行字段配置完成后应用

修改后的完整代码 #

'Creating Pivot Table
Set Psheet = Worksheets(Pvt_shName)
Set Dsheet = Worksheets(ProjectName.End(xlToRight).Value)

Pvt_LR = Dsheet.Cells(Rows.Count, 1).End(xlUp).Row
Pvt_LC = Dsheet.Cells(1, Columns.Count).End(xlToLeft).Column
'Set Range
Set PRange = Dsheet.Cells(1, 1).Resize(Pvt_LR, Pvt_LC)
Set PCache = ActiveWorkbook.PivotCaches.Create(xlDatabase, SourceData:=PRange)

' 刷新透视缓存，确保加载完整数据源
PCache.Refresh

'Creat Pivot Table
' 修正原代码语法错误：TableName参数需用:=赋值
Set Pvt = PCache.CreatePivotTable(TableDestination:=Psheet.Cells(1, 1), TableName:="PO&CO_Tracking")

With Psheet.PivotTables("PO&CO_Tracking")
    ' 关闭手动更新，让字段配置实时生效
    .ManualUpdate = False
    
    ' 设置行字段
    With .PivotFields("Work Type")
        .Orientation = xlRowField
        .Position = 1
    End With
    With .PivotFields("Vendor")
        .Orientation = xlRowField
        .Position = 2
    End With
    
    ' 设置值字段
    With .PivotFields("Approved Purchase Orders (A)")
        .Orientation = xlDataField
        .Position = 1
    End With
    With .PivotFields("Approved Change Orders (B)")
        .Orientation = xlDataField
        .Position = 2
    End With
    With .PivotFields("Total Committed (C = A + B)")
        .Orientation = xlDataField
        .Position = 3
    End With
    With .PivotFields("Invoiced (D)")
        .Orientation = xlDataField
        .Position = 4
    End With
    With .PivotFields("Balance Remaining (E = C - D)")
        .Orientation = xlDataField
        .Position = 5
    End With
    
    ' 格式设置
    .ShowTableStyleRowStripes = True
    .TableStyle2 = "PivotStyleMedium14"
    .RowAxisLayout xlCompactRow
    
    ' 强制刷新透视表
    .RefreshTable
    ' 恢复手动更新（可选，按需开启）
    .ManualUpdate = True
End With

补充说明 #

• 原代码中CreatePivotTable的TableName参数存在语法错误（缺少=），修正后才能确保透视表名称正确绑定
• 关闭ManualUpdate可避免字段配置过程中多次无效刷新，同时确保所有设置完成后一次性渲染完整数据

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