+ Reply to Thread
Results 1 to 7 of 7

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

  1. #1
    Registered User
    Join Date
    11-15-2006
    Posts
    3

    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

  2. #2
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote 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
    ---
    Si fractum non sit, noli id reficere.

  3. #3
    Registered User
    Join Date
    11-15-2006
    Posts
    3

    clarify

    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.

  4. #4
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote 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.
    Please Login or Register  to view this content.
    (partially tested)

    hth
    ---

  5. #5
    Registered User
    Join Date
    11-15-2006
    Posts
    3

    Thanks!

    Thank you very much, that works well!

    Chel

  6. #6
    Forum Contributor
    Join Date
    03-13-2005
    Posts
    6,196
    Quote Originally Posted by Chel
    Thank you very much, that works well!

    Chel
    Good to see, and thanks for the response.

    ---

  7. #7
    Registered User
    Join Date
    12-09-2006
    Posts
    1

    Graphs on multiple sheets

    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

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1