VBA - Pivot table and charts - Automation

[torquil]

Hi Guys,
I am building a sheet that pulls through h:crying:istoric stock details. This I have achieved and now am working on using that data to build charts.
I have gone down the route of Pivot table /charts.
I have code that automatically drills down the data and creats new sheets and charts BUT I have not been able to get this to work with multiple share details, currently the code below works with 3 shares (JACK,MSFT,GOOGL) this is becasue as i wrote the code i didnt build in andy vaariable naming etc.
And this is where i am stuck, please can you help out pointing me in the right direction to resolve this, I know its converting the code i have to include some loops but I have hit a brick wall trying to work out how /where etc.



Sub Charts2()

ActiveSheet.PivotTables("PivotTable111").PivotCache.Refresh
ActiveSheet.PivotTables("PivotTable111").ShowPages PageField:="Client Shares"
ActiveSheet.move after:=Sheets("Historic")
Range("B4").Select
ActiveSheet.Shapes.AddChart2(233, xlLine).Select
ActiveChart.SetSourceData Source:=Range("GOOGL!$A$3:$C$15")
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SetElement (msoElementChartTitleAboveChart)
Selection.Caption = Range("B1")
ActiveChart.ShowReportFilterFieldButtons = False
ActiveChart.ShowLegendFieldButtons = False
ActiveChart.ShowAxisFieldButtons = False
ActiveChart.ShowValueFieldButtons = False
ActiveChart.Axes(xlCategory).TickLabels.MultiLevel = False
ActiveChart.Legend.Select
Selection.Position = xlTop
ActiveChart.FullSeriesCollection(3).Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0.54
.Visible = msoTrue
.Weight = 1
.Visible = msoTrue
.DashStyle = msoLineDashDot
End With
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1").IncrementLeft -3
ActiveSheet.Shapes("Chart 1").IncrementTop -5.25
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.1884772636, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 1").ScaleHeight 1.0803108808, msoFalse, _
msoScaleFromTopLeft
Sheets("MSFT").Select
ActiveSheet.move after:=Sheets("Historic")
ActiveSheet.Shapes.AddChart2(233, xlLine).Select
ActiveChart.SetSourceData Source:=Range("MSFT!$A$3:$C$15")
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SetElement (msoElementChartTitleAboveChart)
Selection.Caption = Range("B1")
ActiveChart.ShowReportFilterFieldButtons = False
ActiveChart.ShowLegendFieldButtons = False
ActiveChart.ShowAxisFieldButtons = False
ActiveChart.ShowValueFieldButtons = False
ActiveChart.Axes(xlCategory).TickLabels.MultiLevel = False
ActiveChart.Legend.Select
Selection.Position = xlTop
ActiveChart.FullSeriesCollection(3).Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0.54
.Visible = msoTrue
.Weight = 1
.Visible = msoTrue
.DashStyle = msoLineDashDot
End With
Sheets("JACK").Select
ActiveSheet.move after:=Sheets("Historic")
ActiveSheet.Shapes.AddChart2(233, xlLine).Select
ActiveChart.SetSourceData Source:=Range("JACK!$A$3:$C$15")
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SetElement (msoElementChartTitleAboveChart)
Selection.Caption = Range("B1")
ActiveChart.ShowReportFilterFieldButtons = False
ActiveChart.ShowLegendFieldButtons = False
ActiveChart.ShowAxisFieldButtons = False
ActiveChart.ShowValueFieldButtons = False
ActiveChart.Axes(xlCategory).TickLabels.MultiLevel = False
ActiveChart.Legend.Select
Selection.Position = xlTop
ActiveChart.FullSeriesCollection(3).Select
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0.54
.Visible = msoTrue
.Weight = 1
.Visible = msoTrue
.DashStyle = msoLineDashDot
End With
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1").IncrementLeft -3
ActiveSheet.Shapes("Chart 1").IncrementTop -5.25
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveSheet.Shapes("Chart 1").ScaleWidth 1.1884772636, msoFalse, _
msoScaleFromTopLeft
ActiveSheet.Shapes("Chart 1").ScaleHeight 1.0803108808, msoFalse, _
msoScaleFromTopLeft
End Sub

[Paul_Hossler]

I think a good place to start would be to attach a sample workbook so that people don't have to re-type your macro and guess as to what your data looks like

It doesn't have to be 10 years and 100 stocks; maybe just 1 month and 3 stocks

Then you can integrate any suggestions into your macro for all the data

[torquil]

Here is the sheet i am currently working with, I had to strip out a everyrthing but the sheet i am using so it would upload.

the sub is "Charts 2" within the Pivot_Chart_Create_Base_VBA module.

26235

[p45cal]

Try the button in the attached, it runs Charts2b.

It's a shame that the ShowPages statement doesn't include a copy of a linked pivot chart.
It's quite heavy to create a new chart for every sheet added, especially if the chart has more adjustments made to it to make it look the way you want. I was thinking it might be better to have a single base chart, with all adjustments and tweaks to make it just so (not necessarily by code), and copy that to each new sheet created by ShowPages, linking it to the new pivot on each sheet?

[torquil]

Dam p45cal that is amazing... thank you so much you have got me out of a massive hole here. this allows me to move on with my project.
thank you once again, you truely are a Guru