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