MS Excel Macro for Graphing

[EBox]

Hi:

I am a real amateur when it comes to Excel macros and I need to find a way to create a bar graph from a simple table in Excel (I have attached a link to the file - data source is on "new data (2)" tab and desired graph is on the "Chart1" tab.

Can anyone help with writing a macro that will make this chart from this data?

Second question is, as I update this data monthly, the number of rows in the source table will change (e.g. sometimes there will be less rows, sometimes more), so the macro would need to be able to determine how many populated data rows exist and graph off of that.

Last question is, what if I have multiple tabs (same format as the source data) that I want to graph separately. Is there a macro that will parse through all tabs and create graphs for each?

I would really welcome anyone's help on this. Thank you so much.

EBox

 

[SkipVought]

First create your chart manually.

Then turn on your macro recorder and record creating your chart manually.

I'd use ONE CHART to chart data on ANY sheet.

Post your recorded code.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[EBox]

Ok, I did this - see code below. But not sure how I would condition for the future revised source worksheet that may have less or more rows (I don't want to exclude records if there are more records added in the future, and don't want to have blanks in the graph if there are less records...).

And also how to wrap up all tabs in this macro...

Thanks for your help-
EBox

Sub Macro1()
'
' Macro1 Macro
' Macro recorded 5/9/2011 by EB
'

'
Range("A2:J19").Select
Charts.Add
ActiveChart.ChartType = xlColumnClustered
ActiveChart.SetSourceData Source:=Sheets("new data (2)").Range("A2:J19"), _
PlotBy:=xlRows
ActiveChart.SeriesCollection(1).XValues = "='new data (2)'!R1C2:R1C10"
ActiveChart.SeriesCollection(2).XValues = "='new data (2)'!R1C2:R1C10"
ActiveChart.SeriesCollection(3).XValues = "='new data (2)'!R1C2:R1C10"
ActiveChart.SeriesCollection(4).XValues = "='new data (2)'!R1C2:R1C10"
ActiveChart.SeriesCollection(5).XValues = "='new data (2)'!R1C2:R1C10"
ActiveChart.SeriesCollection(6).XValues = "='new data (2)'!R1C2:R1C10"
ActiveChart.SeriesCollection(7).XValues = "='new data (2)'!R1C2:R1C10"
ActiveChart.SeriesCollection(8).XValues = "='new data (2)'!R1C2:R1C10"
ActiveChart.SeriesCollection(9).XValues = "='new data (2)'!R1C2:R1C10"
ActiveChart.SeriesCollection(10).XValues = "='new data (2)'!R1C2:R1C10"
ActiveChart.SeriesCollection(11).XValues = "='new data (2)'!R1C2:R1C10"
ActiveChart.SeriesCollection(12).XValues = "='new data (2)'!R1C2:R1C10"
ActiveChart.SeriesCollection(13).XValues = "='new data (2)'!R1C2:R1C10"
ActiveChart.SeriesCollection(14).XValues = "='new data (2)'!R1C2:R1C10"
ActiveChart.SeriesCollection(15).XValues = "='new data (2)'!R1C2:R1C10"
ActiveChart.SeriesCollection(16).XValues = "='new data (2)'!R1C2:R1C10"
ActiveChart.SeriesCollection(17).XValues = "='new data (2)'!R1C2:R1C10"
ActiveChart.SeriesCollection(18).XValues = "='new data (2)'!R1C2:R1C10"
ActiveChart.Location Where:=xlLocationAsNewSheet
With ActiveChart
.HasTitle = False
.Axes(xlCategory, xlPrimary).HasTitle = False
.Axes(xlValue, xlPrimary).HasTitle = False
End With
End Sub

 

[SkipVought]

Please post a representative sample of data.

Your code above has 18 series x-axis values WITH NO VALUES (y-axis)???



Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[EBox]

I posted the data file in my initial post (see link). THe y-axis is a percent and is populated in the Excel file. Perhaps that was done by default?

 

[SkipVought]

Many of us have company impose security restrictions that prevent us from accessing downloads.

If you want help. please post your sample data here.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[EBox]

ID Level 1 Level 2 Level 3 Level 4 Level 5 Crit Care Obs Codes Total Mcare 4 Total Mcare 5
13 0.012484395 0.043695381 0.446941323 0.310861423 0.1360799 0.008739076 0.041198502 0.289855072 0.362318841
19 0.024390244 0.024390244 0.585365854 0.195121951 0.170731707 0 0 0.4 0.4
60 0.019230769 0 0.307692308 0.317307692 0.288461538 0.038461538 0.028846154 0.04 0.72
65 0 0.016759777 0.301675978 0.279329609 0.251396648 0.005586592 0.145251397 0.184210526 0.578947368
BM 0.004668534 0.015873016 0.304388422 0.295985061 0.278244631 0.046685341 0.054154995 0.23890785 0.464163823
CQ 0.005112474 0.024539877 0.319018405 0.249488753 0.236196319 0.065439673 0.100204499 0.193965517 0.495689655
D1 0 0.029411765 0.242647059 0.319852941 0.257352941 0.040441176 0.110294118 0.225352113 0.450704225
GJ 0.002442002 0.05006105 0.271062271 0.301587302 0.234432234 0.04029304 0.1001221 0.258883249 0.497461929
JZ 0 0.032258065 0.322580645 0.322580645 0.258064516 0.064516129 0 0 0.625
KJ 0.020408163 0 0.530612245 0.204081633 0.081632653 0.102040816 0.06122449 0.125 0.375
MD 0.004524887 0.019004525 0.27239819 0.191855204 0.333936652 0.056108597 0.122171946 0.126315789 0.575438596
ML 0.007874016 0.019685039 0.220472441 0.417322835 0.208661417 0.051181102 0.07480315 0.288135593 0.474576271
NG 0.032911392 0.050632911 0.286075949 0.293670886 0.192405063 0.058227848 0.086075949 0.202020202 0.393939394
UP 0 0.007751938 0.227390181 0.310077519 0.219638243 0.087855297 0.147286822 0.08 0.426666667
UR 0.009174312 0.013761468 0.155963303 0.52293578 0.183486239 0.009174312 0.105504587 0.469387755 0.285714286
UT 0.030769231 0.030769231 0.353846154 0.353846154 0.169230769 0.030769231 0.030769231 0.416666667 0.416666667
WB 0 0 0.216216216 0.378378378 0.324324324 0.027027027 0.054054054 0.416666667 0.583333333
YW 0.00729927 0.02189781 0.240875912 0.240875912 0.291970803 0.03649635 0.160583942 0.029411765 0.676470588

 

[SkipVought]

So now tell me how you plan to plot this data. You stated a 'bar' chart, which plots like this...
[tt]

Total Mcare 5 |:::::::::
Total Mcare 4 |::::::::::::
Obs Codes |::::::::
Crit Care |::::::::::::::
Level 5 |::::
Level 4 |:::::::
Level 3 |::::::::::
Level 2 |::::::::::::
Level 1 |::::
+----------------------
[/tt]
with each x-axis (vertical) category having 18 series: a rather BUSY and seemingly useless chart story, IMHO.

Skip,
[sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[EBox]

Sorry, I meant "Column". Each of the IDs above would be represented in the Legend, with each of the columns above shown on the x-axis. The y-axis would show the % value for each ID/Column above.