Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable and PivotCharts Problem - Manipulating in VBA
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
PivotTable and PivotCharts Problem - Manipulating in VBA
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 -- Message posted from http://www.ExcelForum.com |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Pivottable code problem | Excel Discussion (Misc queries) | |||
problem with pivotTable in Excel | Excel Discussion (Misc queries) | |||
Have problem in Manipulating Workbook Object and make macro shortest as possible | Excel Worksheet Functions | |||
Manipulating Data Problem | Excel Worksheet Functions | |||
Pivottable Problem | Charts and Charting in Excel |