Macro to make the same graphs on multiple sheets

[Closed]
Report
-
Hello

I have multiple sheets of data, all with the same formatting (although different numbers of data sets) formatted as a table. I am trying to create a macro which I can create on Sheet 1 and then run on subsequent sheets, which will take its data from the total row of the table on each sheet. When I try this it simply uses the data from sheet 1 each time. Thank you for your time :)

This macro makes three graphs

Sub AllGraphs()
'
' AllGraphs Macro
'

'
ActiveWindow.SmallScroll Down:=21
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveWindow.SmallScroll Down:=-9
ActiveSheet.Shapes("Chart 1").IncrementLeft 298.5
ActiveSheet.Shapes("Chart 1").IncrementTop -76.5
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Values = "='All Company Data'!$AC$484:$AK$484"
ActiveChart.SeriesCollection(1).XValues = "='All Company Data'!$AC$2:$AK$2"
ActiveChart.Legend.Select
Selection.Delete
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
Selection.Caption = "Average Percentage Change (%)"
ActiveChart.SetElement (msoElementChartTitleAboveChart)
Selection.Caption = _
"Average Percentage Change across CF1 and CF2 data for all questions"
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 31
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 37
ActiveWindow.SmallScroll Down:=6
ActiveChart.ChartType = xlColumnClustered
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 28
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 25
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 15
ActiveWindow.ScrollColumn = 16
ActiveWindow.ScrollColumn = 17
ActiveWindow.ScrollColumn = 18
ActiveWindow.ScrollColumn = 19
ActiveWindow.ScrollColumn = 20
ActiveWindow.ScrollColumn = 21
ActiveWindow.ScrollColumn = 22
ActiveWindow.ScrollColumn = 23
ActiveWindow.ScrollColumn = 24
ActiveWindow.ScrollColumn = 26
ActiveWindow.ScrollColumn = 27
ActiveWindow.ScrollColumn = 29
ActiveWindow.ScrollColumn = 30
ActiveWindow.ScrollColumn = 32
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 37
ActiveWindow.ScrollColumn = 36
ActiveWindow.ScrollColumn = 35
ActiveWindow.ScrollColumn = 34
ActiveWindow.ScrollColumn = 33
ActiveWindow.ScrollColumn = 32
Range("AR500").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveSheet.Shapes("Chart 2").IncrementLeft 328.5
ActiveSheet.Shapes("Chart 2").IncrementTop -28.5
ActiveWindow.SmallScroll Down:=-6
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""Improved"""
ActiveChart.SeriesCollection(1).Values = "='All Company Data'!$AL$484:$AT$484"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=""Worsened"""
ActiveChart.SeriesCollection(2).Values = "='All Company Data'!$AU$484:$BC$484"
ActiveChart.SeriesCollection(2).XValues = "='All Company Data'!$AL$2:$AT$2"
ActiveChart.SeriesCollection(1).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 204, 102)
.Transparency = 0
.Solid
End With
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 0, 0)
.Transparency = 0
End With
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
.Solid
End With
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 204, 102)
.Transparency = 0
.Solid
End With
ActiveChart.SeriesCollection(2).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
.Solid
End With
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 0, 0)
.Transparency = 0
End With
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
Selection.Caption = "Percentage of total cases (%)"
ActiveWindow.SmallScroll Down:=-3
ActiveChart.SetElement (msoElementChartTitleAboveChart)
Selection.Caption = _
"The percentage of total cases showing improvement or worsening"
ActiveWindow.ScrollColumn = 38
ActiveWindow.ScrollColumn = 40
ActiveWindow.ScrollColumn = 41
ActiveWindow.ScrollColumn = 42
ActiveWindow.ScrollColumn = 43
ActiveWindow.ScrollColumn = 44
ActiveWindow.ScrollColumn = 45
ActiveWindow.ScrollColumn = 46
ActiveWindow.ScrollColumn = 47
ActiveWindow.ScrollColumn = 48
ActiveWindow.ScrollColumn = 49
ActiveWindow.ScrollColumn = 50
ActiveWindow.ScrollColumn = 51
ActiveWindow.ScrollColumn = 52
ActiveWindow.ScrollColumn = 53
ActiveWindow.ScrollColumn = 54
ActiveWindow.ScrollColumn = 55
ActiveWindow.ScrollColumn = 56
Range("BK490").Select
ActiveSheet.Shapes.AddChart.Select
ActiveChart.ChartType = xlColumnClustered
ActiveSheet.Shapes("Chart 3").IncrementLeft 12
ActiveSheet.Shapes("Chart 3").IncrementTop 98.25
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(1).Name = "=""Improved"""
ActiveChart.SeriesCollection(1).Values = "='All Company Data'!$BD$484:$BK$484"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).Name = "=""Worsened"""
ActiveChart.SeriesCollection(2).Values = "='All Company Data'!$BL$484:$BS$484"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(3).Name = "=""Stayed the same"""
ActiveChart.SeriesCollection(3).Values = "='All Company Data'!$BT$484:$CA$484"
ActiveChart.SeriesCollection(3).XValues = "='All Company Data'!$BL$2:$BS$2"
ActiveChart.SeriesCollection(1).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 204, 102)
.Transparency = 0
.Solid
End With
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 0, 0)
.Transparency = 0
End With
ActiveChart.SeriesCollection(2).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 0, 0)
.Transparency = 0
.Solid
End With
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 0, 0)
.Transparency = 0
End With
ActiveChart.SeriesCollection(3).Select
With Selection.Format.Fill
.Visible = msoTrue
.ForeColor.RGB = RGB(255, 192, 0)
.Transparency = 0
.Solid
End With
With Selection.Format.Line
.Visible = msoTrue
.ForeColor.RGB = RGB(0, 0, 0)
.Transparency = 0
End With
ActiveChart.SetElement (msoElementPrimaryValueAxisTitleRotated)
Selection.Caption = "Percentage improvement in happiness (%)"
ActiveChart.SetElement (msoElementChartTitleAboveChart)
Selection.Caption = _
"Average percentage change in happiness for improvement or worsening in other factors"
ActiveChart.Axes(xlValue).AxisTitle.Select
Selection.Left = 11
Selection.Top = 17.027
ActiveChart.Axes(xlValue, xlPrimary).AxisTitle.Text = _
"Percentage change in happiness (%)"
Selection.Format.TextFrame2.TextRange.Characters.Text = _
"Percentage change in happiness (%)"
With Selection.Format.TextFrame2.TextRange.Characters(1, 34).ParagraphFormat
.TextDirection = msoTextDirectionLeftToRight
.Alignment = msoAlignCenter
End With
With Selection.Format.TextFrame2.TextRange.Characters(1, 34).Font
.BaselineOffset = 0
.Bold = msoTrue
.NameComplexScript = "+mn-cs"
.NameFarEast = "+mn-ea"
.Fill.Visible = msoTrue
.Fill.ForeColor.RGB = RGB(0, 0, 0)
.Fill.Transparency = 0
.Fill.Solid
.Size = 10
.Italic = msoFalse
.Kerning = 12
.Name = "+mn-lt"
.UnderlineStyle = msoNoUnderline
.Strike = msoNoStrike
End With
Range("BQ494").Select
End Sub