Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


pivot table macro

Posted on 2007-08-06
Medium Priority
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 81

Accepted Solution

byundt earned 1600 total points
ID: 19642261
Try adding the following line near the end of your macro:
   pt.PivotFields("Weeks Open").ShowAllItems = True


Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

: Microsoft Office Collaborate for free and online versions of Microsoft  Word, Excel, Powerpoint, OneNote, Onedrive , Email, Calendar etc. In short we can say that Microsoft office is a suite of servers, applications and services developed by  Micr…
Excel can be a tricky bit of software to get your head around. Whilst you’ll be able to eventually get to grips with the basic understanding of how to get by, there are a few Excel tips that not everybody will even know about let alone know how to d…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

577 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