VBA 插入的数据透视表字段和值列均未显示

我想显示一个字段及其值列，以便我可以过滤数据透视表中的字段列.

I wanted to display a field and its values column so I can filter the field column within a pivot table.

我第一次使用记录宏选项是因为我不太了解 VBA.然后我把它清理干净了.这是我记录的操作:

I first used the record macro option because I don't know VBA very well. Then I cleaned it up. Here is the actions I recorded:

• 从我的数据透视表中删除一个字段(Present")
• 向我的数据透视表添加一个字段(周")
• 将相同的字段添加到 VALUES 创建的区域另一列名为周数"
• 更改了周数"到一周总和"
• 隐藏周总和"列出现在
• 过滤字段周"；对于

结束录制

我对 VBA 了解不多，但我正在通过清理我所做的录音来学习.下面是代码的样子(经过一些清理):

I don't know a lot about VBA but I'm learning through cleaning up the recordings I do. Here is what the code looks like (after some cleaning up):

Sub NewHires()
    ' NewHires Macro

    Sheets("CrewSheets").PivotTables("PivotTable1").PivotFields("Present").Orientation = _
                                                                                       xlHidden
    With Sheets("CrewSheets").PivotTables("PivotTable1").PivotFields("Week")
        .Orientation = xlRowField
        .Position = 9
    End With
    
    Sheets("CrewSheets").PivotTables("PivotTable1").AddDataField Sheets("CrewSheets").PivotTables( _
        "PivotTable1").PivotFields("Week"), "Sum of Week", xlSum
    
    With Sheets("CrewSheets").PivotTables("PivotTable1").PivotFields("Sum of Week")
        .Caption = "Sum of Week"
    End With
    
    Columns("J:J").EntireColumn.Hidden = True
    
    Sheets("CrewSheets").PivotTables("PivotTable1").PivotFields("Week").PivotFilters.Add2 _
        Type:=xlValueIsLessThan, DataField:=Sheets("CrewSheets").PivotTables("PivotTable1"). _
        PivotFields("Sum of Week"), Value1:=11
        
End Sub

问题是周"字段被周数"替换.字段，因此不会过滤.我有一种感觉，它与名称有关，而 VBA 没有看到数据透视表字段周"和周"之间的区别.和表值周总和".

The problem is, the "Week" field is replaced by the "Sum of Week" field and thus won't filter. I have a feeling that it has to do with the names and VBA not seeing a difference between the pivot table field "week" and table value "Sum of week".

如果我可以过滤数据透视表放置周总和"的列在，那么这可能是一个修复.但我不能.我需要在数据透视表中过滤它，这正是我手动执行时所做的.

If I could just filter the column that the pivot table puts "Sum of Week" in, then that could be a fix. But I can't. I need to have it filter in the pivot table which is exactly what it does when I manually do it.

-更新如下-

宏之前:

宏之后

它应该是什么样子

为了澄清，我没有像往常一样隐藏列 J:J，在它应该是什么样子"中图片.

To add clarification, I did not hide column J:J like I normally would, in the "what it should look like" picture.