Excel 2003 vba hiding totals for some pivot items and not others
 

Dear Group,

I have a somewhat strange request. I would like to hide some subtotals
and not others in a pivot table.

The following code will generate a pivot table summary of some
hypothetical sales records. Salesmen A sells both chairs and tables,
and salesman B sells tables in region A and B, so the salesmen level
totals are meaningful. However, salesmen C and D only sell tables in
one region, so the total is not informative. However, I don't want to
hide the details since then I also lose the details that C and D sell
tables, and in which region. What I want to do is hide to totals, but
keep the details, which is the opposite of the usual thing.

Furthermore, in some cases, I will want to hide the totals for
salesmen like A, since I will only want totals when there are multiple
entries in the first subcategory.

This may not seem important, but imagine that there are a lot of
salesmen, and many more salesmen are of type C and D, so it wastes
rows, and that there are many items and regions and it is important to
know which a given salesman handles, and we want to print it all out
and not have lots of nearly identical total rows.

Is there a way do this from VBA?

thanks,

code follows:

Sub makeDataset()
Range("A1").Select
ActiveCell.FormulaR1C1 = "Salesman"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Region"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Item"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Cost"

Range("A2:A1000").Select
Selection.FormulaR1C1 = _
"=CHOOSE(MOD(Row(), 4)+1, ""A"",""B"",""C"",""D"")"

Range("B2:B1000").Select
Selection.FormulaR1C1 = _
"=CHOOSE(MOD(Row(), 8)+1,
""north"",""south"",""east"",""east"",""north"","" north"",""east"",""east"")"

Range("C2:C1000").Select
Selection.FormulaR1C1 = _
"=CHOOSE(MOD(Row(), 8)+1,
""chair"",""table"",""table"",""table"",""table"", ""table"",""table"",""table"")"

Range("d2:d1000").Select
Selection.FormulaR1C1 = _
"=CHOOSE(MOD(Row(), 4)+1, 20,20,20,20)"
End Sub
Sub createPivotTable()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"Sheet1!R1C1:R1000C4").createPivotTable TableDestination:="",
TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With
ActiveSheet.PivotTables("PivotTable2").PivotFields ("Salesman")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Region")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Item")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable2").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Cost"), "Sum of Cost", xlSum
ActiveWorkbook.ShowPivotTableFieldList = False
Range("G9").Select

ActiveSheet.PivotTables("PivotTable2").PivotFields ("Region").Subtotals
= Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
End Sub

Excel 2003 vba hiding totals for some pivot items and not others
 

What you can do is hide any row where the cell above the total has a
value (Grand Total being an exception). For instance, D Total has D
above it, so the row with D Total can be hidden. Similarly for C Total,
but not B and A Total. As you might have noticed, I would work my way up
in the VBA loop.

I'm sure you've considered it, but this report just screams wanting to
have the Salesman or the Region and Item in the column instead of all
three in the row. That would solve your problem as well.

Steve


wrote:
Dear Group,

I have a somewhat strange request. I would like to hide some subtotals
and not others in a pivot table.

The following code will generate a pivot table summary of some
hypothetical sales records. Salesmen A sells both chairs and tables,
and salesman B sells tables in region A and B, so the salesmen level
totals are meaningful. However, salesmen C and D only sell tables in
one region, so the total is not informative. However, I don't want to
hide the details since then I also lose the details that C and D sell
tables, and in which region. What I want to do is hide to totals, but
keep the details, which is the opposite of the usual thing.

Furthermore, in some cases, I will want to hide the totals for
salesmen like A, since I will only want totals when there are multiple
entries in the first subcategory.

This may not seem important, but imagine that there are a lot of
salesmen, and many more salesmen are of type C and D, so it wastes
rows, and that there are many items and regions and it is important to
know which a given salesman handles, and we want to print it all out
and not have lots of nearly identical total rows.

Is there a way do this from VBA?

thanks,

code follows:

Sub makeDataset()
Range("A1").Select
ActiveCell.FormulaR1C1 = "Salesman"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Region"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Item"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Cost"

Range("A2:A1000").Select
Selection.FormulaR1C1 = _
"=CHOOSE(MOD(Row(), 4)+1, ""A"",""B"",""C"",""D"")"

Range("B2:B1000").Select
Selection.FormulaR1C1 = _
"=CHOOSE(MOD(Row(), 8)+1,
""north"",""south"",""east"",""east"",""north"","" north"",""east"",""east"")"

Range("C2:C1000").Select
Selection.FormulaR1C1 = _
"=CHOOSE(MOD(Row(), 8)+1,
""chair"",""table"",""table"",""table"",""table"", ""table"",""table"",""table"")"

Range("d2:d1000").Select
Selection.FormulaR1C1 = _
"=CHOOSE(MOD(Row(), 4)+1, 20,20,20,20)"
End Sub
Sub createPivotTable()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"Sheet1!R1C1:R1000C4").createPivotTable TableDestination:="",
TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With
ActiveSheet.PivotTables("PivotTable2").PivotFields ("Salesman")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Region")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Item")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable2").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Cost"), "Sum of Cost", xlSum
ActiveWorkbook.ShowPivotTableFieldList = False
Range("G9").Select

ActiveSheet.PivotTables("PivotTable2").PivotFields ("Region").Subtotals
= Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
End Sub

Excel 2003 vba hiding totals for some pivot items and not others
 

Unfortunately, I think there are far too many salesmen, regions and
items in the real problem- there would be dozens of columns either
way.

I think that selectively hiding rows may be the way to go. Thanks for
suggesting a very simple strategy to accomplish this, one that does
not depend upon the structure of the pivot table.

On Apr 10, 1:10 am, steveh wrote:
What you can do is hide any row where the cell above the total has a
value (Grand Total being an exception). For instance, D Total has D
above it, so the row with D Total can be hidden. Similarly for C Total,
but not B and A Total. As you might have noticed, I would work my way up
in the VBA loop.

I'm sure you've considered it, but this report just screams wanting to
have the Salesman or the Region and Item in the column instead of all
three in the row. That would solve your problem as well.

Steve

wrote:
Dear Group,

I have a somewhat strange request. I would like to hide some subtotals
and not others in a pivot table.

The following code will generate a pivot table summary of some
hypothetical sales records. Salesmen A sells both chairs and tables,
and salesman B sells tables in region A and B, so the salesmen level
totals are meaningful. However, salesmen C and D only sell tables in
one region, so the total is not informative. However, I don't want to
hide the details since then I also lose the details that C and D sell
tables, and in which region. What I want to do is hide to totals, but
keep the details, which is the opposite of the usual thing.

Furthermore, in some cases, I will want to hide the totals for
salesmen like A, since I will only want totals when there are multiple
entries in the first subcategory.

This may not seem important, but imagine that there are a lot of
salesmen, and many more salesmen are of type C and D, so it wastes
rows, and that there are many items and regions and it is important to
know which a given salesman handles, and we want to print it all out
and not have lots of nearly identical total rows.

Is there a way do this from VBA?

thanks,

code follows:

Sub makeDataset()
Range("A1").Select
ActiveCell.FormulaR1C1 = "Salesman"
Range("B1").Select
ActiveCell.FormulaR1C1 = "Region"
Range("C1").Select
ActiveCell.FormulaR1C1 = "Item"
Range("D1").Select
ActiveCell.FormulaR1C1 = "Cost"

Range("A2:A1000").Select
Selection.FormulaR1C1 = _
"=CHOOSE(MOD(Row(), 4)+1, ""A"",""B"",""C"",""D"")"

Range("B2:B1000").Select
Selection.FormulaR1C1 = _
"=CHOOSE(MOD(Row(), 8)+1,
""north"",""south"",""east"",""east"",""north"","" north"",""east"",""east"")"

Range("C2:C1000").Select
Selection.FormulaR1C1 = _
"=CHOOSE(MOD(Row(), 8)+1,
""chair"",""table"",""table"",""table"",""table"", ""table"",""table"",""table"")"

Range("d2:d1000").Select
Selection.FormulaR1C1 = _
"=CHOOSE(MOD(Row(), 4)+1, 20,20,20,20)"
End Sub
Sub createPivotTable()
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase,
SourceData:= _
"Sheet1!R1C1:R1000C4").createPivotTable TableDestination:="",
TableName:= _
"PivotTable2", DefaultVersion:=xlPivotTableVersion10
ActiveSheet.PivotTableWizard
TableDestination:=ActiveSheet.Cells(3, 1)
ActiveSheet.Cells(3, 1).Select
ActiveWorkbook.ShowPivotTableFieldList = True
With
ActiveSheet.PivotTables("PivotTable2").PivotFields ("Salesman")
.Orientation = xlRowField
.Position = 1
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Region")
.Orientation = xlRowField
.Position = 2
End With
With ActiveSheet.PivotTables("PivotTable2").PivotFields ("Item")
.Orientation = xlRowField
.Position = 3
End With
ActiveSheet.PivotTables("PivotTable2").AddDataFiel d
ActiveSheet.PivotTables( _
"PivotTable2").PivotFields("Cost"), "Sum of Cost", xlSum
ActiveWorkbook.ShowPivotTableFieldList = False
Range("G9").Select

ActiveSheet.PivotTables("PivotTable2").PivotFields ("Region").Subtotals
= Array( _
False, False, False, False, False, False, False, False, False,
False, False, False)
End Sub