Code to help consolidate last column used across multiple tabs

Long time lurker, first time poster! Please be gentle :)

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.

I have tinkered with editing the code from Excel VBA to insert sheet name on each row when combining data tables with variable columns but I am a total hack and so far have been unsuccessful.

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.

enter image description here

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).

enter image description here

Any and all help is greatly appreciated!!

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. Hope this clarify's what I am hunting for. Again, any help is greatly appreciated as I hack my way through this. :)

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("Summary") ' defines WsSummary
    LastRowSummary = wsSummary.Cells(wsSummary.Rows.Count, "A").End(xlUp).Row + 1 ' identifys the last used row on target summary sheet
    wsSummary.Range("A2:ZZ" & LastRowSummary).Clear 'clears a set range on target summary sheet
    For Each Ws In ThisWorkbook.Worksheets
        Select Case Ws.Name
            Case "Summary", "Category", "TOC", "Index"
                'If it's one of these sheets, do nothing
            Case Else
            LastRowWs = Ws.Cells(Ws.Rows.Count, "A").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, "A").End(xlUp).Row + 1 'first empty row
            Ws.Range(Split(Cells(, LastColWs).Address, "$")(1) & "2:" & Split(Cells(, LastColWs).Address, "$")(1) & LastRowWs).Copy Destination:=wsSummary.Range("A" & StartRowSummary)
            LastRowSummary = wsSummary.Cells(wsSummary.Rows.Count, "A").End(xlUp).Row
            wsSummary.Range("B" & StartRowSummary & ":B" & LastRowSummary) = Ws.Name ' returns sheet name
            wsSummary.Range("C" & StartRowSummary & ":C" & LastRowSummary) = LastColWs 'returns column #
            wsSummary.Range("D" & StartRowSummary & ":D" & LastRowSummary) = Split(Cells(, LastColWs).Address, "$")(1) 'returns Column letter
        End Select
    Application.ScreenUpdating = True
End Sub

Read more here:

Content Attribution

This content was originally published by Chad at Recent Questions - Stack Overflow, and is syndicated here via their RSS feed. You can read the original post over there.

%d bloggers like this: