Question

关于用Excel VBA创建数据透视表的问题，最终结果不对啊，百思不得其解，求专家解答！！！！

把基本工资表和销售额表合成一个工资表，工资为基本工资加提成，提成为销售额乘以0.05，在合成的透视表中，在“列”字段中添加“提成"项，并且隐藏掉”销售额“项，具体代码如下
Sub 连接数据区域()
On Error GoTo esc
Dim month As Integer
month = InputBox("请输入所要制作工资表的月份")
If (month < 1) Or (month > 3) Then GoTo esc
Dim sells As String
Dim base As String
Dim rownum As Integer
rownum = Worksheets(CStr(month) & "月销售业绩").Range("A1").CurrentRegion.Rows.Count
sells = "'" & CStr(month) & "月销售业绩'!"
sells = sells & "R1C2:R" & CStr(rownum) & "C3"
rownum = Worksheets("基本工资").Range("A1").CurrentRegion.Rows.Count
base = "'基本工资'!"
base = base & "R1C1:R" & CStr(rownum) & "C4"
Dim tablename As String
tablename = CStr(month) & "月工资表"
ActiveWorkbook.PivotCaches.Add(SourceType:=xlConsolidation, SourceData:=Array(Array(sells, "项1"), Array(base, "项2"))).CreatePivotTable tabledestination:="", tablename:=tablename
ActiveSheet.PivotTableWizard tabledestination:=ActiveSheet.Cells(1, 1)
ActiveSheet.Name = tablename
With ActiveSheet.PivotTables(tablename)
.DataPivotField.PivotItems("求和项：值").Position = 1
With .PivotFields("列")
.CalculatedItems.Add "提成", "=销售额*0.05", True
.PivotItems("销售额").Visible = False
End With
End With

ActiveWorkbook.ShowPivotTableFieldList = False
Exit Sub
esc: MsgBox ("输入数字错误或工作簿中已有数据透视表")
End Sub

可是其中 With .PivotFields("列")
.CalculatedItems.Add "提成", "=销售额*0.05", True
.PivotItems("销售额").Visible = False
End With
这一段代码没有执行，导致在“列”字段中没有添加“提成"项，并且没有隐藏掉”销售额“项

Answer 1

CalculatedItems是PivotTable的不是PivotFields的.
所以删掉这两句前后的with和endwith语句试试看.

Answer 2

透视表不合适VBA