本人参考《Excel数据透视表应用大全》这本书第9章9.3新字段自动进入数据透视表布局的VBA代码方法,代码如下(代码完全按照书上写的): Private Sub Worksheet_Activate() Dim pv As PivotTable, rng As Range, dFld As PivotField Set pv = Sheet1.[b3].PivotTable pv.RefreshTable For Each rng In Worksheets("数据源").Range("Data").Rows(1).Cells If VBA.InStr(1, strFld, "," & VBA.Trim(rng)) = 0 Then _ pv.AddDataField pv.PivotFields(rng.Value), "" & rng.Value, xlSum Next rng pv.ManualUpdate = False Application.ScreenUpdating = True End Sub Private Sub Worksheet_Deactivate() Dim pv As PivotTable Set pv = Sheet1.[b3].PivotTable For Each dFld In pv.PivotFields strFld = strFld & "," & dFld.Name Next End Sub
总是出现如下错误:
不知什么原因,麻烦高手不吝赐教,谢谢
|