chart series macro

[Tony7659]

I have a line chart that feeds from data as below. Is there a way to write a
macro that automatically feeds the chart with the latest 13 weeks of data?
(only percentages above 0.0%). Thanks. Tony.

Week Total
1 59.06%
2 71.68%
3 47.80%
4 48.63%
5 60.44%
6 79.66%
7 61.81%
8 88.14%
9 78.80%
10 94.35%
11 119.80%
12 149.79%
13 118.44%
14 105.85%
15 0.0%
16 0.0%
17 0.0%

 

[ryguy7272]

This is just a general suggestion; you will have to tailor it to your
specific needs:

Sub BuildBigChart()
ActiveSheet.ChartObjects.Delete
Dim myChtObj As ChartObject
'
Set myChtObj = ActiveSheet.ChartObjects.Add _
(Left:=500, Width:=800, Top:=50, Height:=1500)

myChtObj.Chart.SetSourceData Source:=Sheets("Sheet1").Range("Begin:End")
myChtObj.Chart.ChartType = xlBarClustered

Set two named ranges: 'Begin' and 'End'.

HTH,
Ryan---

 

[Tony7659]

ryguy,
My line chart is already set-up where the Y-axis represents the percentages
and the X-axis shows the week #s. The source data for the chart is in the tab
named: "Lead Time" (A4:A56 for the week #s and B4:B56 for percentages). I'd
like the macro to automatically capture the latest 13 rolling weeks of data
from the data source tab "Lead Time". As I have 53 weeks, the chart should
only show the latest 13 with percentages bigger than zero. Thanks. Tony.

 

[ryguy7272]

The code that I posted should do that. You will have to modify a few things
for your specific scenario. IF you don't know how to do it, send the file to
me and I'll do it for you.


HTH,
Ryan---

 

[Tony7659]

That sounds great. How can I attach a file to this post?

 

[ryguy7272]

Whoops; thought I included my email address on that last post.
(e-mail address removed)

Ryan---

 

[Tony7659]

Thank you ryguy. I already sent you the file.

Whoops; thought I included my email address on that last post.
(e-mail address removed)

Ryan---