Excel macro to get SEPARATE graphs of many Ys with the same Xs

Question

I have a data set in which the first column is the x but the other columns (> 50) are the Ys. There are 6 rows. I know I can plot all the Ys on the same axis in the same chart, but I need separate graphs of Y all with the same x. However, I am clueless if it is possible to have an excel macro that will allow me to plot DIFFERENT bar graphs for as many Ys all at once. Doing it manually is a pain because I have many datasets with these dimensions. Any help would be greatly appreciated. Below is the code I used but it plotted all the bars in a single graph. Found this code online and tweaked it

Sheets("sheet1").Select
 ActiveSheet.Shapes.AddChart.Select
 With ActiveChart
 .ChartType = xlColumnClustered
.SetSourceData Source:=Sheets("sheet1").Range("A1:D7")  'sets source  data for graph including labels
.SetElement (msoElementLegendRight)  'including legend
.HasTitle = True
'dimentions & location:
.Parent.Left = 47   'defines the coordinates for the left side of the chart
.Parent.Height = 300
.Parent.Width = 600
.ChartTitle.Text = "Yield by group " & intGraphStart
End With

A sample

A screenshot or depiction of your data would be helpful, as well as any attempt at the code you have tried to this point. So is not a "Code for Me site" — Scott Craner, Nov 17 '15 at 17:28

score 0 · Accepted Answer · answered Nov 18 '15 at 04:38

0

The procedure below create a chart in a separated sheet for each of the columns from B to L using column A as a common horizontal axis.

Sub Rng_Charts_Add_Multiple()
Const kChrTtl As String = "Yield by group "
Dim Wbk As Workbook, WshSrc As Worksheet
Dim rSrc As Range, Chrt As Chart
Dim sCol As String, bCol As Byte

    Rem Application Settings
    Application.EnableEvents = False
    Application.DisplayAlerts = False
    Application.ScreenUpdating = False

    Set Wbk = ThisWorkbook
    Set WshSrc = Wbk.Sheets("Sht(1)")
    Application.Goto WshSrc.Cells(1), 1
    Set rSrc = WshSrc.Cells(1).CurrentRegion

    For bCol = 2 To rSrc.Columns.Count

        Rem Add Chart in a New Sheet
        sCol = rSrc.Cells(1, bCol).Value2
        With Wbk
            On Error Resume Next
            .Sheets("Chart " & sCol).Delete
            On Error GoTo 0
            Application.Goto WshSrc.UsedRange.SpecialCells(xlCellTypeLastCell).Offset(2, 2)
            Set Chrt = .Charts.Add2(After:=.Sheets(.Sheets.Count), NewLayout:=1)
        End With

        Rem Chart Settings
        With Chrt
            .name = "Chart " & sCol
            .ApplyLayout (1)
            .ChartTitle.Text = kChrTtl & sCol
            .ChartType = xlColumnClustered
            .SetSourceData Source:=Union(rSrc.Columns(1), rSrc.Columns(bCol))

    End With: Next

    Rem Application Settings
    Application.EnableEvents = True
    Application.DisplayAlerts = True
    Application.ScreenUpdating = True

End Sub

Suggest to visit the following pages:

Variables & Constants, Application Object (Excel), Excel Objects

With Statement, For...Next Statement, Range Object (Excel)

Worksheet Object (Excel), ChartObjects Members (Excel)

answered Nov 18 '15 at 04:38

EEM

6,237
2
15
33

tried running the code you graciously provided but I keep getting the error: Compile error: Method or data member not found. Any ideas as to why? – Nolage86 Nov 18 '15 at 17:54
does it highlights any line? – EEM Nov 18 '15 at 17:56
sorry I forgot to mention that! Yes, the very first line. – Nolage86 Nov 18 '15 at 17:57
Sub Rng_Charts_Add_Multiple() – Nolage86 Nov 18 '15 at 17:57
Ni idea... Did you copy the procedure in a new module? – EEM Nov 18 '15 at 18:00
That's weird... let's try this select all (Ctrl+A) in the module and comment (Comment block) then Compile VBA project. It should compile OK. After this Uncomment all then compile again.... – EEM Nov 18 '15 at 18:06
compiled well. after uncommenting and compiling, the error came on again. – Nolage86 Nov 18 '15 at 18:17
have no idea. try changing the name of the procedure. If this does not work, could you share your file so I can look at it. The code has nothing out of the ordinary... and it seems like excel is expecting that procedure to be something else... – EEM Nov 18 '15 at 18:21
I found this [VBA - Compile Error - Method or Data member not Found](http://stackoverflow.com/questions/27411399/microsoft-excel-activex-controls-disabled) Did you run any update lately? – EEM Nov 18 '15 at 18:41
Changing name didn't fix it either. Unfortunately, my work computer restricts where and what I can do online so I can't use file sharing extensions or systems. However, the sample I posted in my initial post is exactly how my data is arranged. I just have many more columns. You know I was also thinking it is probably my version of excel (2007), but I am unsure. I updated Java yesterday and that's all. It probably would work on my personal computer with a newer version. I will try it when I get home. The info on the link might be helpful. I will go through that as well. I'd give you a feedback – Nolage86 Nov 18 '15 at 18:44
I usually have issues with R on my work computer as well. I am somewhat certain it would run on my personal computer. Appreciate the code, I will get back to you on my findings – Nolage86 Nov 18 '15 at 18:47
Aren't you the best? It worked on my personal computer! I told ya! Thank you again, EEM! – Nolage86 Nov 18 '15 at 21:39

Excel macro to get SEPARATE graphs of many Ys with the same Xs

1 Answers1