creating graph from multiple worksheets

**inuklm** · 06-28-2009, 09:29 AM

Dear All,

I am trying to create a graph to compile data from different sheets in a master sheet. I can easily do this for one sheet; but I don't know/can't find the correct syntax to loop through the sheet names. Any help would be much appreciated - new to VB so apologies if this is a really basic problem.

currently the code looks like this..where "test1" should be a k related syntax so I can loop through sheets test1 to test5- but I have no idea what?

Sub Button3_Click()
Dim k As Integer

Sheets("Sheet1").Select
ActiveSheet.Shapes.AddChart.Select

For k = 1 To 5
ActiveChart.SeriesCollection.NewSeries
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection(1).XValues = "='test1'!$E:$E"
ActiveChart.SeriesCollection(1).Values = "='test1'!$F:$F"
Next k

End Sub

Many Thanks for any help or links to useful info that you may have,
inuklm

**Andy Pope** · 06-29-2009, 05:26 AM

Your post does not comply with Rule 3 of our Forum RULES. Use code tags around code. Posting code without them makes your code hard to read and difficult to be copied for testing. Highlight your code and click the # at the top of your post window. For more information about these and other tags, found here

**Andy Pope** · 06-29-2009, 05:26 AM

Something like this replacing fixed text with a variable

Code:

Sub Button3_Click()
Dim k As Integer
Dim strName As String

Sheets("Sheet1").Select
ActiveSheet.Shapes.AddChart.Select

For k = 1 To 5
    strName = Worksheets(k).Name
ActiveChart.SeriesCollection.NewSeries
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection(1).XValues = "='" & strName & "'!$E:$E"
ActiveChart.SeriesCollection(1).Values = "='" & strName & "'!$F:$F"
Next k

End Sub

**inuklm** · 07-01-2009, 07:43 AM

Hi Andy - sorry for that, many thanks for you're reply, I will give it a try.

Thanks,
inuklm

**inuklm** · 07-02-2009, 07:18 AM

Dear Andy/All,

The problem I am having now is that the macro will plot the first series, create new series for my assigned "k" but will not plot the data for the others? any thoughts would be much appreciated.

I had similar issues before; my thinking was that I hadn't set the loop up properly; but it seems this was not the case,

Thanks, inuklm

below is code I am using,

Code:

Sub Button1_Click()
Dim k As Integer
Dim strName As String
Application.ScreenUpdating = False
Sheets("Sheet4").Select
ActiveSheet.Shapes.AddChart.Select

For k = 1 To 3
    strName = Worksheets(k).Name
ActiveChart.SeriesCollection.NewSeries
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection(1).XValues = "='" & strName & "'!$c2:$c10"
ActiveChart.SeriesCollection(1).Values = "='" & strName & "'!$d2:$d10"
Next k

Application.ScreenUpdating = True
End Sub

**Andy Pope** · 07-02-2009, 07:38 AM

It would help if you could post example workbook of your data layout

**inuklm** · 07-02-2009, 08:06 AM

Sure, please find attached, an example workbook. Sheets1-3 are data and Sheet4/button1 enables macro.

Thanks for your help Andy,
inuklm

**Andy Pope** · 07-02-2009, 08:11 AM

You also need to change the seriescollection index value.
Currently all data ranges are being applied to the first series.

Code:

For k = 1 To 3
    strName = Worksheets(k).Name
ActiveChart.SeriesCollection.NewSeries
ActiveChart.ChartType = xlXYScatterSmoothNoMarkers
ActiveChart.SeriesCollection(k).XValues = "='" & strName & "'!$c2:$c10"
ActiveChart.SeriesCollection(k).Values = "='" & strName & "'!$d2:$d10"
Next k

This change uses the newseries object

Code:

Sub Button1_Click()
Dim k As Integer
Dim strName As String
Application.ScreenUpdating = False
Sheets("Sheet4").Select
ActiveSheet.Shapes.AddChart.Select

For k = 1 To 3
    strName = Worksheets(k).Name
    With ActiveChart.SeriesCollection.NewSeries
        .ChartType = xlXYScatterSmoothNoMarkers
        .XValues = "='" & strName & "'!$c2:$c10"
        .Values = "='" & strName & "'!$d2:$d10"
    End With
Next k

Application.ScreenUpdating = True
End Sub