PDA

View Full Version : Loop through a Pivot to create Charts



kreepa
01-23-2008, 09:07 PM
Whats Up Board,

I have a pivot table that contains a two pages: Cost Center and Resource

Here it is for context:

http://i18.photobucket.com/albums/b146/sbullard/rcmexcelsnag.png


How do I manipulate my record macro to loop through each pivot page "Resource" item and produce a new work sheet and area graph. Here is my recorded code:

I used the show pages to export all the resource items onto a seperate sheet.

* I would like to name each sheet as the Resource pivot item it was created from. Charts would equal "resource pivot item" Chart.

* I want to create a pivot chart from each of the resource pivot items sheets that were created. There should not be any need to rearrange the format. It took me a good while to merge the data and consolidate the data into this friendly format...trust me.

* How do I refer to the pivot chart title as the pivot item it was created from?

I'd Appreciate any help or pointers.
Sub Macro5()
'
' Macro5 Macro
' Macro recorded 1/23/2008 by Kreepa
'
'
ActiveSheet.PivotTables("PivotTable1").ShowPages PageField:="Resource"
Range("B8").Select
Charts.Add
ActiveChart.SetSourceData Source:=Sheets("Clinical Operations VP").Range("B8" _
)
ActiveChart.Location Where:=xlLocationAsNewSheet
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).Select
ActiveChart.SeriesCollection(3).ChartType = xlLineMarkers
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).Select
ActiveChart.SeriesCollection(2).ChartType = xlLineMarkers
Sheets("Chart1").Select
Sheets("Chart1").Name = "="
Sheets("Clinical Operations VP").Select
Sheets("Clinical Operations VP").Name = "Clinical Operations VP"
Sheets("=").Select
Sheets("=").Name = "Clinical Operations VP Chart"




End Sub

Bob Phillips
01-24-2008, 04:44 AM
Probably help to post a workbook.

kreepa
01-24-2008, 07:03 AM
I have attached a sample workbook.

kreepa
01-24-2008, 12:16 PM
Maybe this will work with a better code? How do I modify this to loop through each but create a pivot chart on a new page for each resource in the loop?


Sub LoopPivotPageFields()
Dim pt As PivotTable, ptf As Variant, pti As Variant
For Each pt In ActiveSheet.PivotTables
pt.PivotFields
For Each ptf In pt.PageFields
For Each pti In pt.PivotFields(ptf.Name).PivotItems
MsgBox "Pivot Page Field: " & ptf.Name & " - Pivot Page Field Item: " & pti.Name
Next pti
Next ptf
Next pt
End Sub