1

My problem is that I want to make many graph in one chart but the data is from different sheets.

At the moment my code can only take multi data from one sheet, meaning I can plot 2 graph from one sheet. My code at the moment is: 

Sub ChartSheet()

    Dim ChartSheet1 As Chart
    Set ChartSheet1 = Charts.Add
    With ChartSheet1
        .SetSourceData Source:=Sheets("Sheet1").Range("E12:E6232, Y12:Y6232")
        .ChartType = xlLine
        .HasTitle = True
        .ChartTitle.Characters.Text = "Test Chart"
        .Axes(xlCategory, xlPrimary).HasTitle = True
        .Axes(xlCategory, xlPrimary).AxisTitle.Characters.Text = "x"
        .Axes(xlValue, xlPrimary).HasTitle = True
        .Axes(xlValue, xlPrimary).AxisTitle.Characters.Text = "y"
    End With

End Sub

What I want is to say: 

.SetSourceData Source:=Sheets("Sheet1").Range("E12:E6232, Y12:Y6232")
.SetSourceData Source:=Sheets("Sheet2").Range("D12:E23")
.SetSourceData Source:=Sheets("Sheet3").Range("Y12:Y6232, G27:G496, H3:5977")

and so on.. But when I do this my code it only print the last line from .SetSoureData

Hope some of you can help me work around this, Many thank in advarnce :)

Update: I found abit of a work around by looping but this is not my total answer But here is my other code: 

Sub MultiSheetPlot()

Dim cht As Chart, s As Series, xRng As Range
Dim i As Long, chartName As String

    Set cht = Charts.Add
    cht.ChartType = xlLine


    For i = 1 To 3

        chartName = "Sheet" & i
        Set xRng = Sheets("Sheet1").Range("A1:A20, C1:C20")

        With cht.SeriesCollection.NewSeries()
            .Values = xRng
            .Name = chartName
        End With

    Next i

End Sub

the problem in this code is that it ignores the last range I define like the C10:C20

Improve this question
8
  • 1
    My suggestion is to map the data ranges from the multiple sheets into a single, contiguous range on a single sheet and use that as the source. I don't believe Excel can handle discontinuous ranges across sheets. Commented Aug 31, 2016 at 12:54
  • @JessieQuick you can create a chart, and later on create each Series and correlate it's source data range to your desired range. will that solution works for you? Commented Aug 31, 2016 at 12:58
  • @Shai Rado Thanks for that idear! it works perfect for me and still fast with no excel crash! Commented Aug 31, 2016 at 13:32
  • @JessieQuick so you have it resolved ? you can asnwer your own post Commented Aug 31, 2016 at 13:59
  • I will post a resolve as soon as I have a more beautiful code for my fix around from @Shai Rado's idear. Commented Aug 31, 2016 at 14:13

2 Answers 2

Reset to default
2

Responding to your latest update, I have a Sub that creates a series each time it's called from MultiSheetPlot Sub.

You can actually add more parameters to this Sub (as long as you remeber to pass them in the Calling).

Option Explicit

Dim cht                             As Chart

Sub MultiSheetPlot()

Set cht = Charts.Add
cht.ChartType = xlLine

' call the series creation chart function (each time for each series you want to add to the existing chart
Call Create_SeriesChart("Sheet1", Sheets("Sheet1").Range("E12:E6232"), 1, True, msoThemeColorText1)
Call Create_SeriesChart("Sheet1", Sheets("Sheet1").Range("Y12:Y6232"), 1, True, msoThemeColorText1)

End Sub

' ------ this Sub creates a series to the chart, it receives the following parameters: ------
' 1. seriesName - String
' 2. serValues - Range
' 3. lineWeight - Double (the weight of the line)
' 4. lineVis - Boolean (if you want to hide a certail series)
' 5. lineColor - MsoColorType (using the current's PC Theme colors

Sub Create_SeriesChart(seriesName As String, serValues As Range, lineWeight As Double, lineVis As Boolean, lineColor As MsoColorType)

Dim Ser                             As Series

Set Ser = cht.SeriesCollection.NewSeries

With Ser
    .Name = seriesName
    .Values = serValues

    .Format.Line.Weight = lineWeight
    If lineVis = True Then
        .Format.Line.Visible = msoTrue
    Else
        .Format.Line.Visible = msoFalse
    End If
    .Format.Line.ForeColor.ObjectThemeColor = lineColor   ' Line color Black

End With

End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

Union() will plot the two graph to one graph, since last night i was thinking is it possible to make all the sheets into an array of sheets with range?
I thought the whole idea you want to merge the Range is to have them in the same series, you dont ? Range("A1:A20") suppost to be one series, and Range("C1:C20") suppost to be another series on the chart ?
no, the idea is to plot like 2 graph from sheet1 (with out merging them) and one graph from sheet2.. etc
@JessieQuick try the edited code, I think you'll like it
0

My solution is use Name of plage, you can give a name of your range
Like in my photo : enter image description here

Everytime when you want to use these datas you just need call them Like this plage i gived them name Poste_EtatDeLaDemande

enter image description here

When i want to use these elements i just call it like Range("Poste_EtatDeLaDemande") Excel will find it you don't need tell them where it is anymore ; )

Improve this answer

4 Comments

example in the .Range("Poste_EtatDeLaDemande") you only need the data from 34-100 how til this work out?
Your plage can be bigger when you insert a new line in it, what ever if you wan to get 34 to 100 you can use 'range("poste_etatdelademande").row' get the position of the first line of your plage 'poste_etatdelademande' and then you can calculate which line you want
The problem with this is that I cant just do the same on a new workbook contain the same data, I need to go in and plage every new workbook?
Yes, that what I did with my project.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.