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
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