title: Creating a summary sheet from multiple sheets
body: <p><strong>PROBLEM</strong></p>
  
  <p>I want to iterate through all sheets and create a summary sheet. I have a dynamic column, whereas, some header is on <code>COLUMN X</code> while some on <code>COLUMN AG</code>. </p>
  
  <p>So instead of selecting the specific column, I decided to get the specific header name in order to address that issue. </p>
  
  <p>However, I can't seem to progress on my code since I don't know how to loop all throughout the sheets, get the column then add it to the summary.</p>
  
  <p><strong>CODE</strong></p>
  
  <pre><code>Public Sub forSummary()
  Dim ws As Worksheet
  Dim lRow As Long
  Dim aCell As Range, rng1 As Range
  
  '~~&gt; Set this to the relevant worksheet
  Set ws = ThisWorkbook.Sheets("JAN 6")
  
  With ws
      '~~&gt; Find the cell which has the name
      Set aCell = .Range("A1:BA1").Find("TOTAL OUT")
  
      '~~&gt; If the cell is found
      If Not aCell Is Nothing Then
          '~~&gt; Get the last row in that column and check if the last row is &gt; 1
          lRow = .Range(Split(.Cells(, aCell.Column).Address, "$")(1) &amp; .Rows.Count).End(xlUp).Row
  
          If lRow &gt; 1 Then
              '~~&gt; Set your Range
              Set rng1 = .Range(aCell.Offset(1), .Cells(lRow, aCell.Column))
              '~~&gt; This will give you the address
              rng1.Select
          End If
      End If
  End With
  End Sub
  </code></pre>
  
  <p>Any thoughts?</p>