pivot table macro
Posted on 2007-08-06
I was using a macro whihc creates a pivot table from an excel sheet. while grouping the data in the pivot table.while grouping,i have few problems. i have grouped issues under <1week,2weeks, 3weeks,etc..
the pivot table displays values with in the (group)range but skips the (group)if there is no value which comes under that group.
Can anyone suggest the changes in this code which doesnt skip the group which does nt have values. if there are no issues for that time frame then i wish the category should show '0' but not skip the category.
i have got this code from jeverist@EE.
the VB code(macro) for the pivot table is as follows:
Sub AddPivot(ByRef PivotSheet As Worksheet, ByRef PivotDestination As Range)
Dim ws As Worksheet, pt_rng As Range, pt As PivotTable
Set ws = PivotSheet
Set pt_rng = Range(ws.[A1], ws.Cells(ws.Rows.Count, "A").End(xlUp).Offset(0, Application.CountA(ws.Rows(1)) - 1))
ActiveWorkbook.PivotCaches.Add(SourceType:=xlDatabase, SourceData:=pt_rng).CreatePivotTable _
Set pt = ws.PivotTables(1)
pt.AddFields RowFields:="Weeks Open"
pt.PivotFields("Closure Date").Orientation = xlDataField
PivotDestination.Offset(1).Group Start:=True, End:=True, By:=1