How do I show a "masterlist" of items from several sheet on one sheet?

I have five sheets in my Excel 2010 workbook.  One column is JOB NUMBER.  How do I have a column on a sixth sheet that pulls all job numbers from that column on all the five worksheets?

Kevin SmithAsked:
Who is Participating?
redmondbConnect With a Mentor Commented:

Please see attached. The code is...
Option Explicit

Sub List_Job_Numbers()
Dim xRows     As Long
Dim xResponse As Long
Dim xLast_Row As Long
Dim xOutput   As Long
Dim xSummary  As Worksheet
Dim xSheet    As Variant

Set xSummary = Sheets("Summary")
xRows = xSummary.UsedRange.Cells(1, 1).Row + xSummary.UsedRange.Rows.Count - 1
If xRows > 1 Then
    xResponse = MsgBox("Data found in the Summary sheet (" & xRows & " rows)." & Chr(10) & "'OK' to delete them, 'Cancel' to quit.", vbOKCancel, "List Job Numbers")
    If xResponse = 2 Then
        MsgBox ("User chose to quit.")
        Exit Sub
    End If
    xSummary.Range("2:" & xRows).Delete
    xRows = 1
End If

Application.ScreenUpdating = False

    For Each xSheet In Array("Sheet1", "Sheet2", "Sheet3", "Sheet4", "Sheet5", "Sheet6")
        With Sheets(xSheet)
            xLast_Row = .UsedRange.Cells(1, 1).Row + .UsedRange.Rows.Count - 1
            If xLast_Row > 1 Then
                .Range("E2:E" & xLast_Row).Copy Destination:=xSummary.Range("A" & xRows + 1)
                xRows = xRows + xLast_Row - 1
            End If
        End With

Application.ScreenUpdating = True

MsgBox ("Run complete - " & xRows - 1 & " entries copied.")

End Sub

Open in new window

Thanks, ksmithscs.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.