Create a chart with data from multiple sheets

[MartinDH]

Hello,

I am new at VBA and I definetly need some help.
I am trying to create a XY graph with data from multiple sheets.
All the sheets have the same structure and different names. The number of sheets can vary from one file to another.

I recorded the macro of what I need but it is only working on this specific case (number of sheet = 3 and specific names).
I want to create a loop that can go through all the sheets of my document and create the plot.
Finally I want to move the graph to a new sheet at the end of the document.

Can anybody help me ?

Sub Macro8()
'
' Macro8 Macro
'


'
ActiveSheet.Shapes.AddChart2(240, xlXYScatterSmoothNoMarkers).Select
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(1).Name = "='Sheet1'!$B$6"
ActiveChart.FullSeriesCollection(1).XValues = "='Sheet1'!$F$31:$F$100"
ActiveChart.FullSeriesCollection(1).Values = "='Sheet1'!$G$31:$G$100"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(2).Name = "='Sheet2'!$B$6"
ActiveChart.FullSeriesCollection(2).XValues = "='Sheet2'!$F$31:$F$100"
ActiveChart.FullSeriesCollection(2).Values = "='Sheet2'!$G$31:$G$100"
ActiveChart.SeriesCollection.NewSeries
ActiveChart.FullSeriesCollection(3).Name = "='Sheet3'!$B$6"
ActiveChart.FullSeriesCollection(3).XValues = "='Sheet3'!$F$31:$F$100"
ActiveChart.FullSeriesCollection(3).Values = "='Sheet3'!$G$31:$G$100"
ActiveChart.Axes(xlCategory).MaximumScale = 1
ActiveChart.SetElement (msoElementLegendRight)
ActiveChart.Location Where:=xlLocationAsNewSheet, Name:="Chart"
Sheets("Chart").Select
Sheets("Chart").Move After:=Sheets(5)


End Sub

Thanks in advance
Martin

 

[Domenic]

Try the following...

Option Explicit

Sub CreateXYChart()


    Dim XYChart As Chart
    Dim currentSeries As Series
    Dim currentWorksheet As Worksheet
    
    'Check whether a sheet is active
    If TypeName(ActiveSheet) = "Nothing" Then
        MsgBox "No sheet is available!", vbExclamation
        Exit Sub
    End If
    
    'Add chart to the active sheet
    Set XYChart = ActiveSheet.Shapes.AddChart2(Style:=240, XlChartType:=xlXYScatterSmoothNoMarkers).Chart
    
    'Remove extra series, if any
    With XYChart
        Do While .SeriesCollection.Count > 0
            .SeriesCollection(1).Delete
        Loop
    End With
    
    'Loop through all worksheets in the active workbook
    For Each currentWorksheet In Worksheets
        'Add a new series to the chart
        Set currentSeries = XYChart.SeriesCollection.NewSeries
        'Name and assign XY values to the series from the current worksheet
        With currentSeries
            .Name = "='" & currentWorksheet.Name & "'!$B$6"
            .XValues = "='" & currentWorksheet.Name & "'!$F$31:$F$100"
            .Values = "='" & currentWorksheet.Name & "'!$G$31:$G$100"
        End With
    Next currentWorksheet
    
    With XYChart
        .Axes(xlCategory).MaximumScale = 1
        .SetElement (msoElementLegendRight)
        .Location Where:=xlLocationAsNewSheet, Name:="Chart"
    End With
    
    ActiveSheet.Move after:=Sheets(Sheets.Count)
    
End Sub

Hope this helps!

 

[MartinDH]

Hello Domenic,

Thanks for the help, that is exactly what I was looking for!

Martin

 

[diegomucino]

Hi im trying to make something like this but whit this two differences

First one
My X column and Y column are related to a third column. This third column needs to satisfy a minor an mayor of

Second one
My name of series need to be the name of the sheet they were extracted

Thanks

 

[Fluff]

Duplicate to: Plot many sheets in scatter chart

Please stop posting the same question multiple times. Per Forum Rules (#12), posts of a duplicate nature will be locked or deleted.

In relation to your question here, I have closed this thread so please continue in the linked thread. If you do not receive a response, you can "bump" it by replying to it yourself, though we advise you to wait 24 hours before doing so, and not to bump a thread more than once a day.