Macros for adjusting graphs every day

[Jimmy D]

I have a number of reporting graphs linked into a spreadsheet which I update
on a daily basis. I then need to adjust where the graphs read to include the
days data. This is tedious and time consuming with 16 graphs to adjust. Is
there a macro or something I could write that would adjust the data range
automatically in excel97?Adjustment could be based on a date easily enough.

 

[carlo]

Did you try to do it with dynamic named ranges?
which adjust themselves according to todays date?

then you can tell excel, that the source data is not
in a fixed range but in your named range.

hth

Carlo

 

[Jon Peltier]

Here are some examples of the technique Carlo is suggesting:

http://peltiertech.com/Excel/Charts/DynamicColumnChart1.html
http://peltiertech.com/Excel/Charts/Dynamics.html
http://peltiertech.com/Excel/Charts/DynamicChartLinks.html

- Jon

 

[Jimmy D]

had a look at the info suggested all looks good however I am still having
problems as I have more than 1 data series on the graph and all examples
presume no data in the cells following on from the last entry. This is not
the case for me.
Some insight:
I am using a copy/paste from MS project to produce 3 graph lines from the
same data. 1 line is planned work. Same line also produces Work carried out
(this is the one I want to adjust daily) third is forecast work (based on an
amount of work predicted to arise from known work and known work still to
carry out). First and third data sets are locked by pasting to separate
worksheets to the second data set.

 

[Jon Peltier]

What I frequently suggest is that users put a copy of the data required for
charting into another range or another sheet. Use copy/paste link so the
data links to the original. Then you can reconfigure the chart source data
by dragging cells around until you have the arrangement you need. Worksheets
are very cheap, cells are very cheap, your time trying to avoid using sheets
and cells to your advantage is very costly and you will also avoid the
results you desire.

Spending five minutes with the data will prevent five hours of frustration.

- Jon
-------
Jon Peltier, Microsoft Excel MVP
Tutorials and Custom Solutions
Peltier Technical Services, Inc. - http://PeltierTech.com
_______

had a look at the info suggested all looks good however I am still having
problems as I have more than 1 data series on the graph and all examples
presume no data in the cells following on from the last entry. This is not
the case for me.
Some insight:
I am using a copy/paste from MS project to produce 3 graph lines from the
same data. 1 line is planned work. Same line also produces Work carried
out
(this is the one I want to adjust daily) third is forecast work (based on
an
amount of work predicted to arise from known work and known work still to
carry out). First and third data sets are locked by pasting to separate
worksheets to the second data set.

 

[Jimmy D]

New fellow from work wrote a macro or 3 to do the job.

Sub Chart_Serie_Change()

Dim shChartSheet As Worksheet
Dim shDataSheet As Worksheet
Dim chChart As Object
Dim sSheetDataName As String
Dim i As Integer
Dim j As Integer

Set shChartSheet = Sheet6
Set shDataSheet = Sheet7

Set chChart = shChartSheet.ChartObjects
j = iColumn(shDataSheet)
sSheetDataName = shDataSheet.Name

If j = 1000 Then Exit Sub

'To change the chart
For Each chChart In shChartSheet.ChartObjects

i = iRow(shDataSheet, chChart)
If i = 1000 Then Exit Sub
shChartSheet.Activate
chChart.Activate
chChart.Chart.SeriesCollection(2).Values = "='" & sSheetDataName & "'!R"
& i & "C3:R" & i & "C" & j


Next chChart

MsgBox ("Charts have been updated")

End Sub

'To find the column the data needs to extend to
Function iColumn(shSheet As Worksheet) As Integer

iColumn = 3

With shSheet

Do While Year(.Range("A1").Cells(1, iColumn).Value) <> Year(Now()) _
Or Month(.Range("A1").Cells(1, iColumn).Value) <> Month(Now()) _
Or Day(.Range("A1").Cells(1, iColumn).Value) <> Day(Now())

iColumn = iColumn + 1
If iColumn > 250 Then
MsgBox ("Please, Check date in data source sheet")
iColumn = 1000
Exit Function
End If

Loop

End With

iColumn = iColumn - 1

End Function

'To find the row the chart refers to
Function iRow(shSheet As Worksheet, chChart_Obj As Object) As Integer

'I needed to start at row 6
iRow = 6
Dim sName As String

sName = chChart_Obj.Name

With shSheet
Do While .Range("B" & iRow).Value <> sName

iRow = iRow + 7

If iRow > 120 Then
MsgBox ("Please, check chart names in data source sheet")
iRow = 1000
Exit Function
End If

Loop

End With

End Function

It seems to work fairly well.