枢轴表错误与Office版本2016

Question

我的Office版本是3652019。在2016年Office代码中运行相同的代码时，它会给我带来季度而不是几个月，所以我有错误。我认为可能是宿舍的。

With ActiveSheet.PivotTables("ResumenRuido").PivotCache
     ActiveSheet.PivotTables("ResumenRuido").PivotSelect "", xlDataAndLabel, True
    ActiveSheet.PivotTables("ResumenRuido").NullString = "0"
    .DisplayNullString = True
    .RefreshOnFileOpen = False
    .MissingItemsLimit = xlMissingItemsDefault
End With
With ActiveSheet.PivotTables("ResumenRuido").PivotFields("Codigo SIC")
    .DisplayNullString = True
    .Orientation = xlRowField
    .Position = 1
End With
With ActiveSheet.PivotTables("ResumenRuido").PivotFields("Fecha")
    .DisplayNullString = True
    .Orientation = xlColumnField
    .Position = 1
End With
ActiveSheet.PivotTables("ResumenRuido").PivotFields("Fecha").AutoGroup
ActiveSheet.PivotTables("ResumenRuido").PivotFields("Quarters").PivotItems( _
    "Qtr1").ShowDetail = True
ActiveSheet.PivotTables("ResumenRuido").PivotFields("Years").PivotItems("2019") _
    .ShowDetail = True
ActiveSheet.PivotTables("ResumenRuido").PivotFields("Fecha").Orientation = _
    xlHidden
ActiveSheet.PivotTables("ResumenRuido").PivotFields("Quarters").Orientation = _
    xlHidden
With ActiveSheet.PivotTables("ResumenRuido").PivotFields("Fecha")
    .Orientation = xlColumnField
    .Position = 2
End With
ActiveSheet.PivotTables("ResumenRuido").PivotFields("Years").ShowDetail = True
Range("F4").Select
ActiveSheet.PivotTables("ResumenRuido").RowGrand = False
Range("E4").Select
ActiveSheet.PivotTables("ResumenRuido").PivotFields("Years").Subtotals = Array( _
    False, False, False, False, False, False, False, False, False, False, False, False)
Range("F10").Select
ActiveSheet.PivotTables("ResumenRuido").AddDataField ActiveSheet.PivotTables( _
    "ResumenRuido").PivotFields("Penalidad"), "Sum of Penalidad", xlSum
ActiveSheet.PivotTables("ResumenRuido").RowAxisLayout xlTabularRow
ActiveSheet.Name = "Resumen_penalidades"
Range("E10").Select
ActiveSheet.PivotTables("ResumenRuido").NullString = ""
Call tabla_resumen_energia
End Sub

Answer 1

首先，我建议使用对您的枢纽的引用来缩短代码。在这两行之后，您可以通过" pt"替换每个" ActiveSheet.pivottables(" resumenruido"("。

Dim pt As PivotTable
Set pt = ActiveSheet.PivotTables("ResumenRuido")

而不是PivotField.AutoGroup，我建议使用专用的Ungroup/group方法来确保您获得所需的期间，e。G。年，季度和月份：

With pt.PivotFields("Fecha")
    ' if already grouped, first ungroup it:
    If .TotalLevels > 1 Then .DataRange.Cells(1).Ungroup
    ' sort it:
    .AutoSort Order:=xlAscending, Field:="Fecha"
    ' group it by wanted periods:
    ' seconds, minutes, hours, days, months, quarters, years
    .DataRange.Cells(1).Group _
        Periods:=Array(False, False, False, False, True, True, True)
End With

分组pivotfield

错误

我可以确认，有一个错误(至少在我的Excel版本中" Office 365 Pro Plus，版本1902，构建11328.20100单击并运行"(。如果我手动选择具有日期的枢轴字段的分组，则最低的期间始终未显示为作为附加的pivotfield。
示例：如果我手工或与我的a分组年/季度/几个月。m。代码，几个月未显示！

---

解决方案

组具有额外的较低时期(在某些Excel版本中未显示(，然后将其隐藏，如果显示为

    .DataRange.Cells(1).Group _
        Periods:=Array(False, False, False, True, True, True, True)
' ...
Dim pf As PivotField
For Each pf In pt.PivotFields
    Select Case pf.Name
    Case "Fecha", "Quarters", "Months", "Days"
        pf.Orientation = xlHidden
    End Select
Next pf