title: Code to help consolidate last column used across multiple tabs
body: <p>I have a series of spreadsheets we use to load parameters into a database for a point and click style of order entry. Some of the spreadsheets have hundreds of sheets and keeping them up to date is constantly a problem. What I am trying to do is insert some code that will go sheet by sheet and compile some data from each tab on to a summary sheet.</p>
  <p>I have tinkered with editing the code from <a href="https://stackoverflow.com/questions/39707622/excel-vba-to-insert-sheet-name-on-each-row-when-combining-data-tables-with-varia">Excel VBA to insert sheet name on each row when combining data tables with variable columns</a> but I am a total hack and so far have been unsuccessful.</p>
  <p>To be brief, I am needing the code to return the sheet name in column A and then scan the first row for the first unused column, then return the contents of that column to column C and the row# it came from to column B on the summary sheet.</p>
  <p><a href="https://i.stack.imgur.com/TDYTG.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/TDYTG.png" alt="enter image description here" /></a></p>
  <p>This sample illustrates how the data is structured, a series of questions with an answer at the end. They are not static, but the questions have headers in Row 1 (sometimes just a space value) but the answers do not, Cell 1 of that column is always blank (null).</p>
  <p><a href="https://i.stack.imgur.com/xxRuy.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/xxRuy.png" alt="enter image description here" /></a></p>
  <p>EDIT: Using several sources here on Stack Overflow, I have managed to piece this together which almost works. Instead of finding the last used column and row, I need to find the first column with a null header (row 1) and the last row before any breaks. This is due to there being notes added to most sheets on the right of the data and possibly below.</p>
  <pre><code>Sub CopyData()
      Application.ScreenUpdating = False
      Dim wsSummary As Worksheet
      Dim LastRowWs As Long
      Dim LastColWs As Long
      Dim LastRowSummary As Long
      Dim StartRowSummary As Long
      Set wsSummary = ThisWorkbook.Worksheets(&quot;Summary&quot;) ' defines WsSummary
      LastRowSummary = wsSummary.Cells(wsSummary.Rows.Count, &quot;A&quot;).End(xlUp).Row + 1 ' identifys the last used row on target summary sheet
      wsSummary.Range(&quot;A2:ZZ&quot; &amp; LastRowSummary).Clear 'clears a set range on target summary sheet
      For Each Ws In ThisWorkbook.Worksheets
          Select Case Ws.Name
              Case &quot;Summary&quot;, &quot;Category&quot;, &quot;TOC&quot;, &quot;Index&quot;
                  'If it's one of these sheets, do nothing
              Case Else
              LastRowWs = Ws.Cells(Ws.Rows.Count, &quot;A&quot;).End(xlUp).Row ' Finds the last column in worksheet
              LastColWs = Ws.Cells(LastRowWs, Columns.Count).End(xlToLeft).Column ' Finds the last used colun in Worksheet - ISSUE, I need first null cell column!
              StartRowSummary = wsSummary.Cells(wsSummary.Rows.Count, &quot;A&quot;).End(xlUp).Row + 1 'first empty row
              Ws.Range(Split(Cells(, LastColWs).Address, &quot;$&quot;)(1) &amp; &quot;2:&quot; &amp; Split(Cells(, LastColWs).Address, &quot;$&quot;)(1) &amp; LastRowWs).Copy Destination:=wsSummary.Range(&quot;A&quot; &amp; StartRowSummary)
              LastRowSummary = wsSummary.Cells(wsSummary.Rows.Count, &quot;A&quot;).End(xlUp).Row
              wsSummary.Range(&quot;B&quot; &amp; StartRowSummary &amp; &quot;:B&quot; &amp; LastRowSummary) = Ws.Name ' returns sheet name
              wsSummary.Range(&quot;C&quot; &amp; StartRowSummary &amp; &quot;:C&quot; &amp; LastRowSummary) = LastColWs 'returns column #
              wsSummary.Range(&quot;D&quot; &amp; StartRowSummary &amp; &quot;:D&quot; &amp; LastRowSummary) = Split(Cells(, LastColWs).Address, &quot;$&quot;)(1) 'returns Column letter
          End Select
      Next
      Application.ScreenUpdating = True
  End Sub
  </code></pre>