title: VBA code which goes through multiple columns and builds charts of them
body: <p>I would need an example code for the following case:</p>
  
  <ol>
  <li>I have multiple sheets in my workbook containing data columns</li>
  <li><p>A Sheet looks like following:</p>
  
  <pre><code>X1 Y1  X2 Y2  ...
  2  100 2  101
  3  110 3  108
  4  115 5  114
  5  116 6  117
  6  120 7  121
  </code></pre></li>
  <li><p>Every sheet has same headers for data (in this case X1 and Y1 etc.).</p></li>
  <li>Every sheet contains only one of each data column headers (Y2 cannot be found twice 
  from current worksheet).</li>
  </ol>
  
  <p>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.</p>
  
  <p>Following data would create one graph to scatter plot chart</p>
  
  <pre><code>   X1 Y1   
     2  100 
     3  110 
     4  115 
     5  116 
     6  120 
  </code></pre>
  
  <p>And this one would create another graph to the same scatter plot chart.</p>
  
  <pre><code>     X2 Y2
       2  101
       3  108
       5  114
       6  117
       7  121
  </code></pre>
  
  <p>So all the data graphs has separate x axis values as well.</p>
  
  <p>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.</p>
  
  <p>Would the answer be for example a some sort of subroutine that adds new line from given range to given scatter plot chart?</p>
  
  <hr>
  
  <pre><code>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
  </code></pre>
  
  <p>End Sub</p>