[求助]如何用代码隐藏透视表的子汇总项

luo_by

如何用代码隐藏透视表的子汇总项

shizx98

例句：

Range("A8").Select
    ActiveSheet.PivotTables("数据透视表1").PivotFields("领用部门").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
   

luo_by

QUOTE:

以下是引用shizx98在2007-10-5 14:26:27的发言：

例句：

Range("A8").Select
    ActiveSheet.PivotTables("数据透视表1").PivotFields("领用部门").Subtotals = Array(False, _
        False, False, False, False, False, False, False, False, False, False, False)
   

Sub 创建透视表()
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="铜购进"). _
        CreatePivotTable TableDestination:=ActiveSheet.Cells(3, 1), TableName _
        :="数据透视表1", DefaultVersion:=xlPivotTableVersion10
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("数据透视表1").PivotFields("日期")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("数据透视表1").PivotFields("供应商")
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("数据透视表1").PivotFields("品种")
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("数据透视表1").PivotFields("单价(元)")
        .Orientation = xlRowField
        .Position = 3
    End With
    ActiveSheet.PivotTables("数据透视表1").AddDataField ActiveSheet.PivotTables("数据透视表1" _
        ).PivotFields("重量(KG)"), "求和项:重量(KG)", xlSum
    Columns("C:D").Select
    Selection.NumberFormatLocal = _
        "_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * ""-""??_ ;_ @_ "
    Columns("D:D").EntireColumn.AutoFit
    Columns("C:C").ColumnWidth = 8
       ActiveWorkbook.ShowPivotTableFieldList = False
End Sub

如上代码所示，因为有三个行字段，所以供应商和品种这两项都有子汇总，而供应商和品种不知道有多少,你的代码中函数ARRAY()后面的FALSE是否要与供应商、品种个数相等。

yanjie

Sub 创建透视表()

dim nosubtotal as variant

nosubtatal=array(false,false,false,false,false,false,false,false,false,false,false,false)
    ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:="铜购进"). _
        CreatePivotTable TableDestination:=ActiveSheet.Cells(3, 1), TableName _
        :="数据透视表1", DefaultVersion:=xlPivotTableVersion10
    ActiveWorkbook.ShowPivotTableFieldList = True
    With ActiveSheet.PivotTables("数据透视表1").PivotFields("日期")
        .Orientation = xlPageField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("数据透视表1").PivotFields("供应商")

        .subtotals=nosubtatal
        .Orientation = xlRowField
        .Position = 1
    End With
    With ActiveSheet.PivotTables("数据透视表1").PivotFields("品种")

        .subtatals=nosubtatal
        .Orientation = xlRowField
        .Position = 2
    End With
    With ActiveSheet.PivotTables("数据透视表1").PivotFields("单价(元)")
        .Orientation = xlRowField
        .Position = 3
    End With
    ActiveSheet.PivotTables("数据透视表1").AddDataField ActiveSheet.PivotTables("数据透视表1" _
        ).PivotFields("重量(KG)"), "求和项:重量(KG)", xlSum
    Columns("C:D").Select
    Selection.NumberFormatLocal = _
        "_ * #,##0.00_ ;_ * -#,##0.00_ ;_ * ""-""??_ ;_ @_ "
    Columns("D:D").EntireColumn.AutoFit
    Columns("C:C").ColumnWidth = 8
       ActiveWorkbook.ShowPivotTableFieldList = False
End Sub