Auto-updating source data to make the same chart on multiple worksheets

Hello, sorry if this question has been asked before.

I've got a project where there are a LOT of worksheets. Each worksheet has the same basic layout. On the first worksheet I created a graph, which uses data from columns A and B.

I want to create an identical graph on every worksheet, using data still from columns A and B on the relevant worksheet. Obviously I could do this by copying and pasting the original graph onto every worksheet, then formatting every graph to update the source data worksheet reference. This will take ages though, as there are so many worksheets.

Is there another way of doing this so that the worksheet reference automatically updates as I copy and paste the graph?

Many thanks,
Chel

Thank you for your reply. I recorded a macro but I'm afraid I'm not clear about what you mean by using ActiveSheet.name.

I realise I could just open the macro every time I run it and manually change 'sheet3' to 'sheet4' (or whatever), but that wouldn't really seem to be much faster than NOT using a macro and just manually changing the sheet reference within the source data popup box.

Can ActiveSheet.name automatically pick up the name of the worksheet that is currently active without me having to type it in? What I could really do with is a macro that, when run, updates any references in the source data to another worksheet to the name of the current worksheet.

This is the example macro I've currently got; could you be a bit more detailed about how I would change this so that it can tell to use whatever worksheet I'm currently on?

Range("A4:A14").Select
Charts.Add
ActiveChart.ChartType = xlLineMarkers
ActiveChart.SetSourceData Source:=Sheets("Sheet3").Range("A4:A14"), PlotBy _
:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=Sheet3!R4C2:R14C2"
ActiveChart.Location Where:=xlLocationAsObject, Name:="Sheet3"
ActiveChart.HasLegend = False

Thank you.

Thank you very much, that works well!

Chel

This will allow you to create the graphs in every worksheet all in one go:

Sub Graph()
'
Dim J As Integer

On Error Resume Next


For J = 3 To Sheets.Count ' from sheet 2 to last sheet
Sheets(J).Activate ' make the sheet active

With ActiveSheet
sWord = ActiveSheet.Name

Range("C31:C199").Select
Charts.Add
ActiveChart.ChartType = xlLine
ActiveChart.SetSourceData Source:=Sheets(sWord).Range("C31:C199"), PlotBy _
:=xlColumns
ActiveChart.SeriesCollection(1).XValues = "=" & sWord & "!R31C2:R199C2"
ActiveChart.Location Where:=xlLocationAsObject, Name:=sWord
With ActiveChart
.HasTitle = True
.ChartTitle.Characters.Text = ActiveWorkbook
.Axes(xlCategory, xlPrimary).HasTitle = True
.Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "mm"
.Axes(xlValue, xlPrimary).HasTitle = True
.Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "kN"
End With
ActiveChart.HasLegend = False
ActiveChart.PlotArea.Select
Selection.Interior.ColorIndex = xlNone
End With
Next

End Sub