PivotTable and PivotCharts Problem - Manipulating in VBA

[Brad]

Hello.

I'm using Excel 2000.

I've created some pivot tables for reporting purposes. I
intend to use them to replace old fashioned charts and
tables that are manually keyed summary data.

I need to keep the transition to the new spreadsheet as
unobtrustive as possible for my less technical users, so
that they have the same charts that they're used to.

I need to create three charts. A weekly rollup with a
percentages, a weekly rollup with actual dollar amounts,
and a monthly rollup of dollar amounts. All of this data
is beautifully configured in my PivotTable, and very easy
to find and demonstrate trends, etc. When I create the
first chart, and make it weekly, add a calculation field
for percentage, change the chart type, fonts etc, the
PivotChart is perfect. But I glance back at my PivotTable
and it's been completely distorted. It has the same
categories, series, data, and page fields as the chart I
just created. Now...let's say I enter another chart in
its own worksheet (chartsheet?) change it to show dollar
amounts with the same date grouping (a week) and bang! My
pivottable changes again, and my first chart is identical
to my second. I haven't even added the third monthly
rollup Chart.

Debra presented a solution of dynamically changing the
pivotcharts and printing each one after each change was
completed. (Thank you for the idea.) But my users rarely
print the report, they just fiddle with it in Excel. I
modified her idea to use the Workbook_SheetActivate Event
to dynamically modify the pivottable whenever a new
worksheet is selected. I've made some progress, but I've
run into a few roadblocks.

Private Sub Workbook_SheetActivate(ByVal sh As Object)
If sh.Type = xlWorksheet Then
'Call UpdateWorksheet(Sh)
Else
'Call UpdateChart(sh)
Call Temp(sh)
End If
End Sub

Private Sub Temp(sh As Object)
' Instead of updating the chart, let's update the
pivot table.
If sh.Name <> "Weekly Interest Chart" Then
Exit Sub
End If
Dim shtDetailDrilldown As Worksheet
Set shtDetailDrilldown = Worksheets("Detail Drilldown")

Dim pvtTable As PivotTable
Set pvtTable = shtDetailDrilldown.PivotTables
("DetailDrilldown")

Dim itm As PivotItem
Dim fldCheck_Date As PivotField
Set fldCheck_Date = pvtTable.PivotFields("Check_Date")

'fldCheck_Date.DataRange.Ungroup
For Each itm In fldCheck_Date.PivotItems
If Left(itm.Value, 1) = "<" Or Left(itm.Value, 1)
= ">" Then
itm.Visible = False
ElseIf DateValue(itm.Value) < DateValue
("07/01/2003") Then
itm.Visible = False
End If
Next
Dim fldYears As PivotField
' Set fldYears = fldCheck_Date.DataRange.Group(, , 7,
Array(False, False, False, False, False, False, True))
With fldCheck_Date.DataRange
.Group , , 7, Array(False, False, False, False,
False, False, True)
End With
End Sub

My first problem is when I try to group the data and
change the date unit from day by day to Years. I want to
group the day by day value (Check_Date) into Years and 7
day groups. I want to name each of these groups. When I
create a group, it gives it the name Check_Date2. I need
to figure out how to set "fldYears" equal to the new
grouped range.

My second problem is having a second group. But maybe
once I understand the first one, I can figure that out.

Please help.

Thank you.

-Brad

 

[BrianB]

We have used pivot tables extensively here for several years. FWIW her
are a couple of ideas to make them easier to work with.

1. Set up a pivot table manually. Change the base data when necessary
and refresh (using VBA code).

2. Add new data (eg. monthly) to the bottom of the old and use a
additional column for "Month".

3. Add calculated columns etc to the *base data* wherever possible
rather than pt calculated fields. Can use dynamic named ranges.

4. Do not format a pivot table. Use it as a feed between the data and
properly formatted report. The pt can be hidden if required. Usuall
VLOOKUP() is all that is needed.

5. You do not necessarily have to include base data in the sam
workbook (keeps the size down).

6. If you really need to use code for seting up etc. use the macr
recorder (eg. Select, PivotTableWizard method, etc.). This is les
prone to bugs & other 'strange' problems