torquil
03-28-2020, 03:28 AM
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
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