Dynamically move charts on over time as the data changes

Topic

New Reply

Hi

I am using Excel 2010 and I wondered if there was any way to move chart lines dynamically over time.

So, I have data in a row by date, I then draw a line chart based on that data and to that date, as I add additional columns with the date and more data I want to know if I can dynamically increase the line chart to take that data into consideration with out having to go in and change the data range.

The reason I want to do this is because I have loads of charts and it is a pain to go into each and change the range.

Thanks

Mike

Reply | Quote

Replies

Check out this thread

http://windowssecrets.com/forums/showthread//158474-Excel-2010-copying-and-pasting-to-an-existing-graph

Reply | Quote

Check out this thread

http://windowssecrets.com/forums/showthread//158474-Excel-2010-copying-and-pasting-to-an-existing-graph

Hi

Thanks for this and sorry for not responding earlier, this look really great but I have an added dimension which not sure how to apply you code to.

Attached is a cut down example of what I am trying to achieve.

There are 3 tabs

1) Reporting Parameters – this defines the start and end dates for the chart data

2) Totals for the charts – this has rows and rows of data which is the base to the charts, please note that the area’s in yellow are taken from pivot tables by fo this example I have just entered actual values. All non yellow areas are summary totals used for the charts, will also be for other charts as well if I can get this working

3) Total Chart All, CHN, PLN, AND – this has the charts based on the data in 2) above.

So the areas I am getting stuck on are:

1) Get the Chart to change when data is added (which is your code)
2) Doing it for all the charts as they take data from different points in the tab 2) above

I have been trying to work on a macro which creates the data range and updating it but it is becoming messy and my macro skills are failing me… that was not too difficult.

Any suggestions would be great… you may even find it to be a challenge :rolleyes:

Many thanks

Mike

Reply | Quote

Mike,

I revised your file with code that is initiated from a button placed on the “Reporting Parameters” page. Once you enter in your start and end dates, click the button and your charts will be updated reflecting the start and end dates in the X axis and their perspective values.

Hope this is what you are looking to do.

Maud

Code:

Sub UpdateChart()
Application.ScreenUpdating = False
[COLOR=”#008000″]’—————————————-
‘DECLARE VARIABLES[/COLOR]
Dim Parameters As Worksheet
Dim Chrt As Worksheet
Dim Totals As Worksheet
Dim Data As Range
Dim Data1 As Range
Dim Data2 As Range
Dim Data3 As Range
Dim Data4 As Range
Dim Data5 As Range
Dim Data6 As Range
Dim Data7 As Range
Dim Data8 As Range
[COLOR=”#008000″]’—————————————
‘SET WORKSHEETS[/COLOR]
Set Parameters = Worksheets(“Reporting Parameters”)
Set Chrt = Worksheets(“Totals Chart All, CHN, PLN, AND”)
Set Totals = Worksheets(“Totals for the Charts”)
[COLOR=”#008000″]’—————————————
‘VALIDATE DATES[/COLOR]
If Parameters.Range(“C2”) < Parameters.Range("C1") Then
    MsgBox "Your end date cannot be prior to the start date"
    Exit Sub
End If
[COLOR="#008000"]'—————————————
'SET SERIES RANGES[/COLOR]
Totals.Activate
Set Data = Totals.Range(Cells(1, Parameters.Range("C1")), Cells(1, Parameters.Range("C2")))      [COLOR="#008000"]'X VALUES[/COLOR]
Set Data1 = Totals.Range(Cells(221, Parameters.Range("C1")), Cells(221, Parameters.Range("C2"))) [COLOR="#008000"]'SERIES 1 CHART 6[/COLOR]
Set Data2 = Totals.Range(Cells(222, Parameters.Range("C1")), Cells(222, Parameters.Range("C2"))) [COLOR="#008000"]'SERIES 2 CHART 6[/COLOR]
Set Data3 = Totals.Range(Cells(69, Parameters.Range("C1")), Cells(69, Parameters.Range("C2")))   [COLOR="#008000"]'SERIES 1 CHART 7[/COLOR]
Set Data4 = Totals.Range(Cells(70, Parameters.Range("C1")), Cells(70, Parameters.Range("C2")))   [COLOR="#008000"]'SERIES 2 CHART 7[/COLOR]
Set Data5 = Totals.Range(Cells(140, Parameters.Range("C1")), Cells(140, Parameters.Range("C2"))) [COLOR="#008000"]'SERIES 1 CHART 8[/COLOR]
Set Data6 = Totals.Range(Cells(141, Parameters.Range("C1")), Cells(141, Parameters.Range("C2"))) [COLOR="#008000"]'SERIES 2 CHART 8[/COLOR]
Set Data7 = Totals.Range(Cells(211, Parameters.Range("C1")), Cells(211, Parameters.Range("C2"))) [COLOR="#008000"]'SERIES 1 CHART 9[/COLOR]
Set Data8 = Totals.Range(Cells(212, Parameters.Range("C1")), Cells(212, Parameters.Range("C2"))) [COLOR="#008000"]'SERIES 2 CHART 9[/COLOR]
[COLOR="#008000"]'—————————————
'UPDATE CHARTS[/COLOR]
Chrt.Activate
[COLOR="#008000"]'CHART 6[/COLOR]
    ActiveSheet.ChartObjects("Chart 6").Activate
    ActiveChart.SeriesCollection(1).XValues = Data
    ActiveChart.SeriesCollection(1).Values = Data1
    ActiveChart.SeriesCollection(2).Values = Data2
[COLOR="#008000"]'CHART 7[/COLOR]
    ActiveSheet.ChartObjects("Chart 7").Activate
    ActiveChart.SeriesCollection(1).XValues = Data
    ActiveChart.SeriesCollection(1).Values = Data3
    ActiveChart.SeriesCollection(2).Values = Data4
[COLOR="#008000"]'CHART 8[/COLOR]
    ActiveSheet.ChartObjects("Chart 8").Activate
    ActiveChart.SeriesCollection(1).XValues = Data
    ActiveChart.SeriesCollection(1).Values = Data5
    ActiveChart.SeriesCollection(2).Values = Data6
[COLOR="#008000"]'CHART 9[/COLOR]
    ActiveSheet.ChartObjects("Chart 9").Activate
    ActiveChart.SeriesCollection(1).XValues = Data
    ActiveChart.SeriesCollection(1).Values = Data7
    ActiveChart.SeriesCollection(2).Values = Data8
[COLOR="#008000"]'————————————–
'CLEANUP[/COLOR]
Set Parameters = Nothing
Set Chrt = Nothing
Set Totals = Nothing
Set Data = Nothing
Set Data1 = Nothing
Set Data2 = Nothing
Set Data3 = Nothing
Set Data4 = Nothing
Set Data5 = Nothing
Set Data6 = Nothing
Set Data7 = Nothing
Set Data8 = Nothing
Application.ScreenUpdating = True
End Sub

Reply | Quote

Hi Maud,

Couple of questions please.

1) Can I pick out if a chart number is not being used, the reason for the question is that if I (or anybody else) adds charts to that tab they may not know the chart number and the rows whcih the data is in. I was planning to put a loop in going through charts 1 to 20 and using the resume next to skip any errors
2) If a chart is present can I find out the data range row number so I do not have to predefine row numbers
3) Last but not least I will have some charts which are based on multi lines of data and not in one block.

Thanks again and sorry to be a pain.

Regards

Mike

Reply | Quote

Hey Maud

That is fantastic, will play with this.

Many thanks

Mike

Reply | Quote

Hi Maud,

Thanks for your help I have now cracked it and the macro works well and all parameterised.

In the end I worked using the Xformula option instead of the Xvalues.

Regards

Mike

Reply | Quote

If a chart is present can I find out the data range row number so I do not have to predefine row numbers

mikeyt

Here is the code to find the data ranges for the selected chart

37539-datarange

Code:

Public Sub FindSeries()
On Error GoTo errorhandler
[COLOR=”#008000″]’———————————
‘DECLARE AND SET VARIABLES[/COLOR]
Dim Chrt As Chart
Dim DataRange As String
Dim Series As Integer
Dim Xvalues As String
Set Chrt = ActiveChart
Xvalues = “”
[COLOR=”#008000″]’———————————
‘CYCLE THROUGH CHART SERIES AND FIND RANGES[/COLOR]
For Series = 1 To 2
    DataRange = Chrt.SeriesCollection(Series).Formula
    s = Split(DataRange, “,”)
    If Xvalues = “” Then
        t = Split(s(1), “!”)
        Xvalues = t(1)
    End If
    u = Split(s(2), “!”)
    Message = Message & _
           “Series ” & Series & “:” & Chr(13) & _
           ”   Data Range: ” & u(1) & Chr(13)
Next Series
MsgBox “X value Range: ” & Xvalues & Chr(13) & Message
Exit Sub
[COLOR=”#008000″]’———————————
‘NO CHART SELECTED[/COLOR]
errorhandler:
MsgBox “Please select a chart then run the code again”
End Sub

Reply | Quote

Many thanks

Reply | Quote