Adding to macro that creates graph from multiple sheets in excel

Question

I have an excel file with multiple sheets labelled HI12981001.asvp, HI19281002.asvp, HI19281003.asvp etc.

In each sheet are 2 columns with no headers. I am using the macro below to create a scatter graph of the data in these 2 columns from each sheet. The problem is I have to manually change the series name for each sheet.

I am looking for help to build on this macro so that I can get excel to look at a list (i.e. HI12981001.asvp, HI19281002.asvp, HI19281003.asvp.. and create a data series in the main chart for each sheet or simple go through the entire book and create a graph with the series name the same as the sheet name.

Here is the macro for just the first three sheets

Sub zx()
Dim wb As Workbook
Dim sh As Worksheet
Dim Chrt As Chart
Dim Srs As Series

Set wb = ActiveWorkbook
Set Chrt = wb.Charts.Add(After:=wb.Worksheets(wb.Worksheets.Count))
Chrt.ChartType = xlXYScatterSmoothNoMarkers
Chrt.Name = "SVP Profiles"

Set Srs = Chrt.SeriesCollection.NewSeries
Srs.Name = "HI12981001.asvp"
Set sh = wb.Sheets("HI12981001.asvp")
Srs.XValues = "=" & sh.Name & "!" & sh.UsedRange.Columns(2).Address
Srs.Values = "=" & sh.Name & "!" & sh.UsedRange.Columns(1).Address

Set Srs = Chrt.SeriesCollection.NewSeries
Srs.Name = "HI12981002.asvp"
Set sh = wb.Sheets("HI12981002.asvp")
Srs.XValues = "=" & sh.Name & "!" & sh.UsedRange.Columns(2).Address
Srs.Values = "=" & sh.Name & "!" & sh.UsedRange.Columns(1).Address



 Set Srs = Chrt.SeriesCollection.NewSeries
    Srs.Name = "HI12981003.asvp"
    Set sh = wb.Sheets("HI12981003.asvp")
    Srs.XValues = "=" & sh.Name & "!" & sh.UsedRange.Columns(2).Address
 Srs.Values = "=" & sh.Name & "!" & sh.UsedRange.Columns(1).Address


 End Sub

Thank you

Tim Williams Tim Williams · Accepted Answer · 2012-11-20T17:30:42

Option Explicit

Sub zx()

Dim wb As Workbook
Dim Chrt As Chart


   Set wb = ActiveWorkbook
   Set Chrt = wb.Charts.Add(After:=wb.Worksheets(wb.Worksheets.Count))
   Chrt.ChartType = xlXYScatterSmoothNoMarkers
   Chrt.Name = "SVP Profiles"


   'add the series
   AddNewSeries "HI12981001.asvp", wb, Chrt
   AddNewSeries "HI12981002.asvp", wb, Chrt
   AddNewSeries "HI12981003.asvp", wb, Chrt

End Sub

'Add new series to chart 'cht', using sheet 'sheetName'
'    in Workbook 'wb'
Sub AddNewSeries(sheetName As String, wb As Workbook, cht As Chart)

    Dim sh As Worksheet
    Dim Srs As Series

    Set Srs = cht.SeriesCollection.NewSeries
    Srs.Name = sheetName
    Set sh = wb.Sheets(sheetName)
    Srs.XValues = "=" & sh.Name & "!" & sh.UsedRange.Columns(2).Address
    Srs.Values = "=" & sh.Name & "!" & sh.UsedRange.Columns(1).Address

End Sub

Adding to macro that creates graph from multiple sheets in excel

1 Answers