VBA - Pivot table and charts - Automation

[torquil]

Hi Guys,
I am building a sheet that pulls through historic 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.

Share Sheet.xlsm

[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