Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Help with Pivot Table "BaseItem"
Does anyone know of a way to select the "last" BaseItem in a pivot table. I
used the recorder and got this far. I have a named range "Database" that uses CountA to determine the last entry. The data comes from an export. The export will vary, but I will always want to select the calculation on the pivot table to "PercentOf" and will want the last entry to be the "BaseItem". Also if someone knows a cleaner way to write what the recorder produced I would appreciate them suggesting changes. I added the first line so that I could run the macro from any worksheet. I have been working on this project off and on for about a year now and have gotten good feedback from the group. Thanks, Joel Sub CummulativePivot() Worksheets("Data").Activate ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatab ase, SourceData:= _ "Database").CreatePivotTable TableDestination:="", TableName:="PivotTable1" ActiveSheet.PivotTableWizard TableDestination:=ActiveSheet.Cells(3, 1) ActiveSheet.Cells(3, 1).Select ActiveSheet.PivotTables("PivotTable1").SmallGrid = False ActiveSheet.PivotTables("PivotTable1").AddFields RowFields:="Data", _ ColumnFields:="Week Ending" With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Target Early" & Chr(10) & "% Comp.") .Orientation = xlDataField .Position = 1 .Calculation = xlPercentOf .BaseField = "Week Ending" .BaseItem = "26-Sep-04" End With With ActiveSheet.PivotTables("PivotTable1").PivotFields ("Target Late" & Chr(10) & "% Comp.") .Orientation = xlDataField .Calculation = xlPercentOf .BaseField = "Week Ending" .BaseItem = "26-Sep-04" End With End Sub |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Create Pivot Table Data with Column "Sum" rather than "count" defa | Excel Discussion (Misc queries) | |||
Pivot Table Report Filter - "OR" instead of "AND" Multiple Filters | Excel Discussion (Misc queries) | |||
Keep a column of comments "with" pivot table as pivot tables datachanges, causing rows to be added and deleted. | Excel Discussion (Misc queries) | |||
OfficeXP "Cannot open pivot table source file" when refreshing pivot table | Excel Programming | |||
Help required with setting up a pivot table with the source on sheet1 to have the pivot table created on sheet called "report" | Excel Programming |