Populating a chart with Updating information

4/30/2006 5/7/2006 5/14/2006 5/21/2006 5/28/2006
202 129 87 0 0
153 320 54 0 0

I have a VBA Program that is pulling data from a continuously updating
spreadsheet. As you can see after the current week(5/14/2006) there is no
data and thus zeros returned. I need to copy and paste this information into
a preexisting chart. The chart is set up exactly like the information above.
I just need it to past the data from, 4/30, 5/7, 5/14 and then next week
when 5/21 is populated have the data shift and paste 5/7, 5/14, 5/21. So
basically take the date with the last information (no zeros) associated with
it and past that and the previous two weeks data into the chart.
I hope that made since.

Thanks,

Tim

Try:

Sub newrange()
Dim x, y, r
For x = 1 To 200
If Cells(2, x) > 0 Then y = x
Next
Range(Cells(1, y - 2), Cells(3, y)).Select
r = Selection.Address
ActiveSheet.ChartObjects("Diagram 2").Activate ' Set Diagram 2 to yur Shart
name
ActiveChart.ChartArea.Select
ActiveChart.SetSourceData Source:=Sheets("Ark1").Range(r), PlotBy:=xlRows '
Set Ark1 to yur Sheet name
Range("a1").Activate
End Sub

-----------------------------------------------------



"Lost and Looking for Help" skrev:

> 4/30/2006 5/7/2006 5/14/2006 5/21/2006 5/28/2006
> 202 129 87 0 0
> 153 320 54 0 0
>
> I have a VBA Program that is pulling data from a continuously updating
> spreadsheet. As you can see after the current week(5/14/2006) there is no
> data and thus zeros returned. I need to copy and paste this information into
> a preexisting chart. The chart is set up exactly like the information above.
> I just need it to past the data from, 4/30, 5/7, 5/14 and then next week
> when 5/21 is populated have the data shift and paste 5/7, 5/14, 5/21. So
> basically take the date with the last information (no zeros) associated with
> it and past that and the previous two weeks data into the chart.
> I hope that made since.
>
> Thanks,
>
> Tim
>

Excellent, Here is the your code slightly modified to fit my application

Sub newrange()
Dim X As Integer, Y As Integer

For X = 1 To 25
If Cells(2, X) > 0 Or Cells(3, X) > 0 Then Y = X
Next
Range(Cells(1, Y - 10), Cells(3, Y)).Select
Selection.Copy
Sheets("Sheet1").Select
ActiveSheet.Range("A10").Select
Selection.PasteSpecial
End Sub

And here is another example of the Chart that i am copying from and pasting
into specific cells

12/11/2005 12/18/2005 12/25/2005 1/1/2006
2 9 4
0
34 19 3
0

The problem with this code is that sometimes the data that is returned is
going to be both zero's. As you can see that the data is put into weekending
catogeries. I would some how like to use the code to look at the current
date and then copy and paste the cells that are under that date ending. So
if the current date was 12/21/2005 then everything from 12/25/2005 back to
12/11/2005 would be copied and pasted. If the current date is 12/29/2005
then everything from under 1/1/2006 to 12/18/2005be copied and pasted. All
the data that is collected during a week is stored under the last day of that
week.

ok just se ur input
its allmost midtnight here now, so have to wait
until tomorow
then ill look at it
b.reg. ex.