"master" sheet - that is, generate a list combining all lists

Originally Posted by loblibbin

I have multiple worksheets, each of which is the same table but with different data for different sets. (Each has identical columns and types of data within the columns).

I want to have a sheet in my workbook that will go through and list everything from all of the sheets. Is there a decent way of doing this without writing code or manual copy/paste of everything? Lastly, would it be possible to have a column added to the master sheet to identify which worksheet that line came from?

Hi, and no, not without some code.

A macro something like

Sub MergeSheets()
Dim ws As Worksheet
Dim iRow As Long, iNextRow As Long
For Each ws In Worksheets
    If Not ws.Name = "Master" Then
        iNextRow = Sheets("Master").Range("A65536").End(xlUp).Row
        Sheets(ws.Name).Select
        iRow = ActiveSheet.Range("A65536").End(xlUp).Row
        If iRow < 1 Then iRow = 1
        If iNextRow < 1 Then iNextRow = 1
        'MsgBox ws.Name & " " & iRow & " " & iNextRow
        ActiveSheet.Rows("1:" & iRow).Select
        Selection.Copy
        Sheets("Master").Select
        Range("A" & iNextRow + 1).Select
        ActiveSheet.Paste
        Application.CutCopyMode = False
        Sheets("Master").Range("Z" & iNextRow & ":Z" & iNextRow + iRow).Value = ws.Name
        End If
    Next
End Sub

would do the trick.

hth
---