Macros To Update Charts
 

Hi

I have a spreadsheet with 15 charts. Every month new rows of data are added
to the worksheets. I have to update the charts to contain all the previous
data as well as any new data that is added to the worksheet.

e.g. In January, I have Sheet1 with data from A1:C15. I have the chart that
contains this data. Then in February, Sheet1 is updated and now contains data
from A1:C30. I would like to use a macro to add the new data to the chart.
Can someone help me with the code please? Note, that column A always has a
week commencing date in it. So in February, cells A31, A32, A33, etc contain
the date 00-Jan-00 and cells A1:A30 contain proper dates.

Macros To Update Charts
 

Hi Thriller

First of all I'm no expert so this might not be a good way of doing this but
it will probably work...

I would try using the following to find the last row of full data:
Dim lngRow As Long
lngRow = Range("A:A").End(xlDown).Row

you can then use it to build a string representing the range of cells to be
used and assing it to the chart:
Dim strRange As String
strRange = "A2:C" & lngRow
ActiveChart.SeriesCollection(1).XValues = Range(strRange)

I guess the same sort of thing could be applied to other chart properties
such as the data labels and axes etc since these might need to be changed in
order to fir the new data in. There are a few assumption in the code above
such as that there are no gaps between the source data rows and that the user
has selected the chart before running the macro. Obviously this can be
modified to fit a For Each...Next loop to iterate through the charts in your
workbook.

Hope this helps...and I would check it thoroughly before using for real!!!

Andy

"Thriller" wrote:

Hi

I have a spreadsheet with 15 charts. Every month new rows of data are added
to the worksheets. I have to update the charts to contain all the previous
data as well as any new data that is added to the worksheet.

e.g. In January, I have Sheet1 with data from A1:C15. I have the chart that
contains this data. Then in February, Sheet1 is updated and now contains data
from A1:C30. I would like to use a macro to add the new data to the chart.
Can someone help me with the code please? Note, that column A always has a
week commencing date in it. So in February, cells A31, A32, A33, etc contain
the date 00-Jan-00 and cells A1:A30 contain proper dates.

Macros To Update Charts
 

You can do this without using macros. Any of the following links can show
you how.

http://office.microsoft.com/en-us/as...098011033.aspx
http://peltiertech.com/Excel/Charts/Dynamics.html
http://www.tushar-mehta.com/excel/ne...ynamic_charts/


Regards,
Vic Eldridge



"Thriller" wrote:

Hi

I have a spreadsheet with 15 charts. Every month new rows of data are added
to the worksheets. I have to update the charts to contain all the previous
data as well as any new data that is added to the worksheet.

e.g. In January, I have Sheet1 with data from A1:C15. I have the chart that
contains this data. Then in February, Sheet1 is updated and now contains data
from A1:C30. I would like to use a macro to add the new data to the chart.
Can someone help me with the code please? Note, that column A always has a
week commencing date in it. So in February, cells A31, A32, A33, etc contain
the date 00-Jan-00 and cells A1:A30 contain proper dates.