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

Originally Posted by Chel

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

Hi,

You might need to Record a macro, copy the chart, paste to a new sheet, then adjust the range.

This will show a line something like

ActiveChart.SeriesCollection(1).Values = "=Sheet3!R2C3:R20C3"

for which you need to amend the Sheet number/name to be the sheet you are now on, ie ActiveSheet.Name

hth
---

Originally Posted by 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.

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

becomes

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

(partially tested)

hth
---

Originally Posted by Chel

Thank you very much, that works well!

Chel

Good to see, and thanks for the response.

---