pivot table macro

Posted on 2007-08-06
Last Modified: 2013-11-05
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

End Sub
Question by:ark989
    1 Comment
    LVL 80

    Accepted Solution

    Try adding the following line near the end of your macro:
       pt.PivotFields("Weeks Open").ShowAllItems = True


    Featured Post

    Do You Know the 4 Main Threat Actor Types?

    Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

    Join & Write a Comment

    This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
    If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
    Graphs within dashboards are meant to be dynamic, representing data from a period of time that will change each time the dashboard is updated with new data. Rather than update each graph to point to a different set within a static set of data, t…
    This Micro Tutorial will demonstrate in Google Sheets how to use the HYPERLINK function to create live links inside your spreadsheet.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now