+ Reply to Thread
Results 1 to 2 of 2

Change data range in charts

  1. #1
    Tony
    Guest

    Change data range in charts

    I would like to copy a chart that already exists and change the data range to
    show a different set of data. I used the record macro tool and it shows me
    what I basically want to do. The problem is I need to change the "R" value so
    I think I need to use a variable of some sort but the ways I have been doing
    it doesn't work. Here is the sample I recorded:

    Sub test()
    '
    ' test Macro
    '

    '
    Sheets("216-3").Select
    Sheets("216-3").Copy Before:=Sheets(1)
    ActiveChart.PlotArea.Select
    ActiveChart.SeriesCollection(1).Values = "=Data!R6735C4:R6786C4"
    ActiveChart.SeriesCollection(2).Values = "=Data!R6735C5:R6786C5"
    ActiveChart.SeriesCollection(3).Values = "=Data!R6735C9:R6786C9"
    ActiveChart.SeriesCollection(4).Values = "=Data!R6735C11:R6786C11"

    End Sub

    I am going to put this in a loop to create mulitple charts but I need to
    change the R6735 to R6818 as an example but as I mentioned, I am having a
    problem with making a variable work in this situation. If anyone has a
    suggestion I would appreciate it. Thank you in advance.

  2. #2
    K Dales
    Guest

    RE: Change data range in charts

    You don't tell where those new values of R are coming from so I will assume
    you have them stored in variables, e.g. R1 and R2, that are either integer
    values or strings. Then it would be:
    > ActiveChart.PlotArea.Select
    > ActiveChart.SeriesCollection(1).Values = "=Data!R" & R1 & "C4:R" & R2 &"C4"
    > ActiveChart.SeriesCollection(2).Values = "=Data!R" & R1 & "C4:R" & R2 &"C5"
    > ActiveChart.SeriesCollection(3).Values = "=Data!R" & R1 & "C4:R" & R2 & "C9"
    > ActiveChart.SeriesCollection(4).Values = "=Data!R" & R1 & "C4:R" & R2 &"C11"


    "Tony" wrote:

    > I would like to copy a chart that already exists and change the data range to
    > show a different set of data. I used the record macro tool and it shows me
    > what I basically want to do. The problem is I need to change the "R" value so
    > I think I need to use a variable of some sort but the ways I have been doing
    > it doesn't work. Here is the sample I recorded:
    >
    > Sub test()
    > '
    > ' test Macro
    > '
    >
    > '
    > Sheets("216-3").Select
    > Sheets("216-3").Copy Before:=Sheets(1)
    > ActiveChart.PlotArea.Select
    > ActiveChart.SeriesCollection(1).Values = "=Data!R6735C4:R6786C4"
    > ActiveChart.SeriesCollection(2).Values = "=Data!R6735C5:R6786C5"
    > ActiveChart.SeriesCollection(3).Values = "=Data!R6735C9:R6786C9"
    > ActiveChart.SeriesCollection(4).Values = "=Data!R6735C11:R6786C11"
    >
    > End Sub
    >
    > I am going to put this in a loop to create mulitple charts but I need to
    > change the R6735 to R6818 as an example but as I mentioned, I am having a
    > problem with making a variable work in this situation. If anyone has a
    > suggestion I would appreciate it. Thank you in advance.


+ 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