Combining Data VBA

[jpencek3]

I'm a little stuck on this VBA. I currently have 100s of worksheets in a document, each with 1 column (always column A) of names. The name of the worksheet is the name of a client, and the names are who listed are who is authorized on that account. Inefficient, I know, but I didn't put this document together. I'm trying to find a way to move all of this data into a table on 1 single tab, where row 1 is the worksheet title and then listed below it is all the data that was found on that individual workbook.

Example: The first worksheet is named Client A with different names listed in cells A1:A6. The second worksheet is named Client B with different names (some could overlap with Client A) listed in cells A1:A15. This goes on with 100s of worksheets in the workbook, and the names are not sorted at all on any of the tabs. How could I get a new worksheet that has Client A in cell A1, with the names from that tab in cells A2:A7, Client B in cell B1, with the names from that tab in cells B2:B16, Client C in C1, and so on?

 

[offthelip]

This will do it, it creates a sheet called "comp" to put it all on, change this to whatever you want to call the sheet

Sub tst()
Dim ws As Worksheet


With ThisWorkbook
        .Sheets.Add(After:=.Sheets(.Sheets.Count)).Name = "Comp"
End With
 indi = 1
    For Each ws In ActiveWorkbook.Worksheets
        If ws.Name <> "Comp" Then
         lastrow = ws.Cells(Rows.Count, "A").End(xlUp).Row
         With ws
         inarr = Range(.Cells(1, 1), .Cells(lastrow, 1))
         End With
         With Worksheets("comp")
          Range(.Cells(2, indi), .Cells(lastrow + 1, indi)) = inarr
          .Cells(1, indi) = ws.Name
         End With
         indi = indi + 1
        End If
    Next ws
    
End Sub

 

[jpencek3]

Thank you so much!