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.
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).
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 Next Application.ScreenUpdating = True End Sub