Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How do I select multiple items in an Excel 2003 Pivot table | Excel Discussion (Misc queries) | |||
Pivot Table Hiding Totals = Zero | Excel Worksheet Functions | |||
Excel 2003 and 2007 and Select Multiple Items on Pivot AS Cube bug | Excel Discussion (Misc queries) | |||
Showing/Hiding Pivot-Items | Excel Programming | |||
Button Include Hidden Items in Totals (Pivot Table) never enabled | Excel Discussion (Misc queries) |