VBA code which goes through multiple columns and builds charts of them

Question

I would need an example code for the following case:

1. I have multiple sheets in my workbook containing data columns

2. A Sheet looks like following:

   X1 Y1  X2 Y2  ...
   2  100 2  101
   3  110 3  108
   4  115 5  114
   5  116 6  117
   6  120 7  121
   

3. Every sheet has same headers for data (in this case X1 and Y1 etc.).

4. Every sheet contains only one of each data column headers (Y2 cannot be found twice from current worksheet).

I would need a VBA code that finds all the data columns by given data column header and draws graphs of all these data columns to one scatter plot chart AND names the drawn line with current sheet name.

Following data would create one graph to scatter plot chart

   X1 Y1   
   2  100 
   3  110 
   4  115 
   5  116 
   6  120 

And this one would create another graph to the same scatter plot chart.

     X2 Y2
     2  101
     3  108
     5  114
     6  117
     7  121

So all the data graphs has separate x axis values as well.

My main problem is that I for some reason can't find out how to make the scatter plot chart work with VBA. Find the correct data columns I can get to work.

Would the answer be for example a some sort of subroutine that adds new line from given range to given scatter plot chart?

---

Sub createChart()

Dim headerToFind As String
headerToFind = "Y2" 

Dim i As Integer
Dim j As Integer
Dim ws As Worksheet
i = 1
j = 1

' create and initialize scatterplot chart to given worksheet


For Each ws In Workbooks("data.xlsm").Worksheets

    Do While IsEmpty(ws.Cells(1, i)) = False

        If ws.Cells(1, i) = headerToFind Then

           ' get data from column i. x values can be found from column i-1

        End If

        i = i + 1
    Loop
Next ws

End Sub

Answer 1

Your question is pretty broad, but in your comment, you ask more specifically "how to create a scatter plot chart that I can refer to later by its name."

As far as I know, the process of giving a name to a chart is a bit awkward in Excel. The only way I found to do it is to activate the chart by clicking on it, then go in the VBA editor's Immediate window, and type:

ActiveChart.Parent.Name = "My scatter plot" ' or whatever name

Then, assuming you haven't deleted your chart, you can refer to it within your code like this:

Dim cho As chartObject
Dim cht As Chart
Set cho = Sheet1.ChartObjects("My scatter plot") 
Set cht = cho.Chart

This is assuming it is on Sheet1. Adjust as appropriate. Then you can add series to it like this:

Dim ser As Series
Set ser = cht.SeriesCollection.NewSeries
With ser
    'example settings:
    .ChartType = xlXYScatter
    .XValues = Range("A2:A6")
    .Values = Range("B2:B6")
    .Name = Range("B1").Value
    .MarkerStyle = xlMarkerStyleNone
    'can also format the line style, color, weight, etc. etc.
End With

For more detail on Chart and ChartObject objects, have a look at this earlier answer.