Is it possible to compile all data from selected tabs to a new tab and have new tab identify source tab?

[ExcelNoob65]

Hello,

I have a workbook that I am creating to schedule daily trips for my transportation company.

I have a worksheet with a different tab for each driver.

I would like to make a new tab to function as a FULL TRIP LIST tab that has all trip data and identifies which driver has the trip for dispatching purposes without manually entering all the data.

I currently have a FULL TRIP LIST tab but it is not identifying the drivers so to dispatch, we are having to search all tabs to find the trip and I can't imagine that being the most efficient way.

Not sure if this is possible, but I am hoping someone can help! Thank you in advance!

 

[John Davis]

Maybe:

Sub ExcelNoob65()
Dim ws As Worksheet, x As Range, y As Range
With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "FULL TRIP LIST" Then
        With ws
            .UsedRange.Offset(1).Copy Sheets("FULL TRIP LIST").Range("B" & Rows.Count).End(3)(2)
            x = Sheets("FULL TRIP LIST").Range("B" & Rows.Count).End(3).row
            y = Sheets("FULL TRIP LIST").Range("A" & Rows.Count).End(3).row
            Sheets("FULL TRIP LIST").Range(Cells(y, "A"), Cells(x, "A")) = ws.Name
        End With
    End If
Next ws
With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With
End Sub

Note: Column A should have the source sheet's name

 

[ExcelNoob65]

Maybe:

Sub ExcelNoob65()
Dim ws As Worksheet, x As Range, y As Range
With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With
For Each ws In ActiveWorkbook.Worksheets
    If ws.Name <> "FULL TRIP LIST" Then
        With ws
            .UsedRange.Offset(1).Copy Sheets("FULL TRIP LIST").Range("B" & Rows.Count).End(3)(2)
            x = Sheets("FULL TRIP LIST").Range("B" & Rows.Count).End(3).row
            y = Sheets("FULL TRIP LIST").Range("A" & Rows.Count).End(3).row
            Sheets("FULL TRIP LIST").Range(Cells(y, "A"), Cells(x, "A")) = ws.Name
        End With
    End If
Next ws
With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With
End Sub

Note: Column A should have the source sheet's name

Thank you for your reply! I tried to run the code, but it did not work. I believe it is because it should specify which tabs to pull the data from? I have a few other tabs in the worksheet that the code would need to exclude.

The names of the tabs in order are as follows. I have bolded the tabs that I DO NEED the data pulled and the tabs I do not need are not bolded:

Summary
Dan
Al
Bernie
Brendan
Jake
Jamie
Joyce
Mike
Ruth
Terry
backup full trip list
WORKLIST
FRTA Download
Format add on's

Also these tabs have the first 2 rows dedicated to headers. I am not sure if this information is even relavent but wanted to toss it out there so you can better understand what I am trying to do. Sorry I should have included this initially :/

Thank you So much for your help!

 

[John Davis]

Try:

Sub ExcelNoob65()
Dim ws As Worksheet, x As Range, y As Range
With Application
    .Calculation = xlCalculationManual
    .ScreenUpdating = False
End With
For Each ws In ActiveWorkbook.Worksheets
    Select Case ws.Name
        Case Is = "backup full trip list", "WORKLIST", "FRTA Download", "Format add on's"
        With ws
            .UsedRange.Offset(2).Copy Sheets("FULL TRIP LIST").Range("B" & Rows.Count).End(3)(2)
            x = Sheets("FULL TRIP LIST").Range("B" & Rows.Count).End(3).row
            y = Sheets("FULL TRIP LIST").Range("A" & Rows.Count).End(3).row
            Sheets("FULL TRIP LIST").Range(Cells(y, "A"), Cells(x, "A")) = ws.Name
        End With
        Case Else
            GoTo zz
    End Select
zz:
Next ws
With Application
    .Calculation = xlCalculationAutomatic
    .ScreenUpdating = True
End With
End Sub