I have several graphs that shows the past 30 sets of data. Every coupl
days I add new data to the database and I an trying to write a progra
that will automatically change the source data range to show the lates
30 sets of data. I have tried a lot of things but can't get it right.
Anyone have ideas on how they would do this

--
kola556
-----------------------------------------------------------------------
kola5567's Profile: http://www.excelforum.com/member.php...fo&userid=2461
View this thread: http://www.excelforum.com/showthread.php?threadid=39907

If your worksheet was called Summary and the chart was called Chart 9
the example below shows a template for how to define the range:

Sheets("Summary").ChartObjects("Chart 9").Chart.SetSourceDat
Source:=Range("your_range_here"), PlotBy _
:=xlColumns

You can get the syntax for the range by copying the range data from th
chart controls when you update it manually. You can also add variable
to the range to expand it dynamically

--
dazma
-----------------------------------------------------------------------
dazman's Profile: http://www.excelforum.com/member.php...fo&userid=2590
View this thread: http://www.excelforum.com/showthread.php?threadid=39907

I have that line of code, but am having trouble defining the "your rang
here" part. I dont know how to define or set up the controls of th
range since it will always change. The first cell will change, but th
range will always be 30 cell long.

What are the dynamic controls I could try

--
kola556
-----------------------------------------------------------------------
kola5567's Profile: http://www.excelforum.com/member.php...fo&userid=2461
View this thread: http://www.excelforum.com/showthread.php?threadid=39907

In my instance the rows change and the variable NumRows is added outsid
the quotes with the & as follows:

("=Summary!$A$2,Summary!$A$3:$A$" & NumRows
",Summary!$H$2:$I$2,Summary!$H$3:$I$" & NumRows & "")

Your range will be different - If you can copy your current chart dat
and highlight the cell ref you need to change I could show an exampl

--
dazma
-----------------------------------------------------------------------
dazman's Profile: http://www.excelforum.com/member.php...fo&userid=2590
View this thread: http://www.excelforum.com/showthread.php?threadid=39907

You might find it easier to use the "get external data" for this:

1. Build a sheet that will be your input sheet
2. go to a new sheet and "import" your input sheet by following those
steps:
Data/ Import external data/ import data then select the file you are
currently in in the drop-down menu and the sheet that contains your
input data.
3.You should now get on this sheet exactly the same data as on the
input sheet, but it will be referred to with a rangename (should be
name of file).
4.Base your graphs on THIS sheet, not the input sheet.
5. Add new data on the input sheet.
6 Goto the sheet that contains the imported data and rightclick
anywhere on the data. You should get a red "!". If you click this, your
import will be refreshed - and your graphs will refresh automatically.

Hope that helps

You might find it easier to use the "get external data" for this:

1. Build a sheet that will be your input sheet
2. go to a new sheet and "import" your input sheet by following those
steps:
Data/ Import external data/ import data then select the file you are
currently in in the drop-down menu and the sheet that contains your
input data.
3.You should now get on this sheet exactly the same data as on the
input sheet, but it will be referred to with a rangename (should be
name of file).
4.Base your graphs on THIS sheet, not the input sheet.
5. Add new data on the input sheet.
6 Goto the sheet that contains the imported data and rightclick
anywhere on the data. You should get a red "!". If you click this, your
import will be refreshed - and your graphs will refresh automatically.

Hope that helps

You might find it easier to use the "get external data" for this:

1. Build a sheet that will be your input sheet
2. go to a new sheet and "import" your input sheet by following those
steps:
Data/ Import external data/ import data then select the file you are
currently in in the drop-down menu and the sheet that contains your
input data.
3.You should now get on this sheet exactly the same data as on the
input sheet, but it will be referred to with a rangename (should be
name of file).
4.Base your graphs on THIS sheet, not the input sheet.
5. Add new data on the input sheet.
6 Goto the sheet that contains the imported data and rightclick
anywhere on the data. You should get a red "!". If you click this, your
import will be refreshed - and your graphs will refresh automatically.

Hope that helps

No need for VBA. See
Dynamic Charts
http://www.tushar-mehta.com/excel/ne...rts/index.html

--
Regards,

Tushar Mehta
www.tushar-mehta.com
Excel, PowerPoint, and VBA add-ins, tutorials
Custom MS Office productivity solutions

In article ,
says...

I have several graphs that shows the past 30 sets of data. Every couple
days I add new data to the database and I an trying to write a program
that will automatically change the source data range to show the latest
30 sets of data. I have tried a lot of things but can't get it right.
Anyone have ideas on how they would do this?


--
kola5567
------------------------------------------------------------------------
kola5567's Profile: http://www.excelforum.com/member.php...o&userid=24614
View this thread: http://www.excelforum.com/showthread...hreadid=399071