ADDING DATA TO A GRAPH CAN IT BE DONE

[drag-driver]

I need to create a macro which can update a graph with the last entered data on a data sheet, does anyone have any clue how to do this. So far i have the vb code which selects an exact row for my extra graph line but i want it to select the last entered data row,so that i can then run the macro and it will update the graphs accuratly. I as such need to change the referance in the below data range to a floating referance which always selects the last entered data on the required sheet.

Sheets("DSQ401").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).XValues = "='DSQ401 -- Data'!R1C4:R1C26"
ActiveChart.SeriesCollection(2).Values = "='DSQ401 -- Data'!R138C4:R138C26"
ActiveChart.SeriesCollection(2).Name = "='DSQ401 -- Data'!R138C3"
ActiveWindow.Visible = False
Windows("PO_Flare 2.xls").Activate
Sheets("raw data").Select

[Ian Mac]

On 2002-05-21 04:37, drag-driver wrote:
I need to create a macro which can update a graph with the last entered data on a data sheet, does anyone have any clue how to do this. So far i have the vb code which selects an exact row for my extra graph line but i want it to select the last entered data row,so that i can then run the macro and it will update the graphs accuratly. I as such need to change the referance in the below data range to a floating referance which always selects the last entered data on the required sheet.

Sheets("DSQ401").Select
ActiveSheet.ChartObjects("Chart 1").Activate
ActiveChart.SeriesCollection.NewSeries
ActiveChart.SeriesCollection(2).XValues = "='DSQ401 -- Data'!R1C4:R1C26"
ActiveChart.SeriesCollection(2).Values = "='DSQ401 -- Data'!R138C4:R138C26"
ActiveChart.SeriesCollection(2).Name = "='DSQ401 -- Data'!R138C3"
ActiveWindow.Visible = False
Windows("PO_Flare 2.xls").Activate
Sheets("raw data").Select

You can do this just on the worksheet.

I assuming you need the Dynamically change the X Axis and Lables.

set up a Dynamic Named Range.

Goto Insert>Names>Define, type something like DataLables in the top box.
In refers to type
=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)

Change the Sheet cell Ref's if needed, also change to:

=OFFSET(Sheet1!$A$2,0,0,COUNTA(Sheet1!$A:$A)-1,1)

if you need to start in row 2.

Click Add.

Do the Same for DataValues

Changing;

=OFFSET(Sheet1!$A$1,0,0,COUNTA(Sheet1!$A:$A),1)
to
=OFFSET(Sheet1!$B$1,0,0,COUNTA(Sheet1!$A:$A),1).

Now goto the chart wizard.

Select the chart you need.

Goto the Series Tab:

To use the Dynamic Range you need to include the Name of the workbook, so in Values put:

=YourBook.xls!DataValues

and

=YourBook.xls!DataLables

you should now have a dynamic chart.

I probably need to explain further, if you need it just ask.

[drag-driver]

The solution you give is not the one i need, i understand how it works but what i need is a macro which adds a new series to the data not adjusts the series already present.It is tricky to explain.
1. I have a graph which contains a number of series named WW1 to WW5, Each WW# contains x and y data, this results in a graph which allows you to compare ww results with each other.(note the x and y data are stored in rows not colums). X does not change from WW to WW

2. Each week the raw data for which contains the y data is added to the sheets. I curently manualy go to the graph select add series and select the required y and required ww# add to the graph. The required y values are the last entry in the raw data sheets.

3. Since i have about 30 graphs to update i want this process to be automated, and my code above automates everything but i need to set it up such that it selects the last entry for y each time its ran.

4. note it must store the result such that the following week when the macro is ran the previouis data is not erased.

PLease let us know if this is possible or not
cos i dont want to be wasting your time or mine,

Thanks for the help,

Thomas

[Ian Mac]

On 2002-05-21 06:23, drag-driver wrote:
The solution you give is not the one i need, i understand how it works but what i need is a macro which adds a new series to the data not adjusts the series already present.It is tricky to explain.
1. I have a graph which contains a number of series named WW1 to WW5, Each WW# contains x and y data, this results in a graph which allows you to compare ww results with each other.(note the x and y data are stored in rows not colums). X does not change from WW to WW

2. Each week the raw data for which contains the y data is added to the sheets. I curently manualy go to the graph select add series and select the required y and required ww# add to the graph. The required y values are the last entry in the raw data sheets.

3. Since i have about 30 graphs to update i want this process to be automated, and my code above automates everything but i need to set it up such that it selects the last entry for y each time its ran.

4. note it must store the result such that the following week when the macro is ran the previouis data is not erased.

PLease let us know if this is possible or not
cos i dont want to be wasting your time or mine,

Thanks for the help,

Thomas

Sorry, now I understand, this may be one I'm out of my depth with, but as I see it (I could be very wrong (it wouldn't be the 10th time ))

you need to make:

ActiveChart.SeriesCollection(2).Values = "='DSQ401 -- Data'!R138C4:R138C26"

reference the last row if there indeed is one.