Sub TrackingReport()


    Dim LCWB As Workbook
    Dim Trackingbook As Workbook
    Dim i As Integer
    Dim rng As Range
    Dim lastinTrackingbook As Integer
    Dim LastInCurrSheet As Integer
    LCWBLoc = "B:\Loads covered spreadsheet\Loads Covered.xlsm"
    TrackingbookLoc = "B:\Loads covered spreadsheet\Tracking.xlsx"
    Set Trackingbook = Workbooks.Open(TrackingbookLoc)
    Set LCWB = Workbooks.Open(LCWBLoc)
    Application.ScreenUpdating = False
'ReportView
    AllDAtaSheetExist = False
    For Each Sh In Trackingbook.Worksheets
        If Sh.Name = "ALL DATA For Tracking" Then: AllDAtaSheetExist = True
    Next

    If AllDAtaSheetExist = True Then

        Trackingbook.Sheets("ALL DATA For Tracking").UsedRange.Clear

    Else

        Trackingbook.Worksheets.Add().Name = "ALL DATA For Tracking"

    End If


'''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 'so this approach is to evaluate the cell value and opnly copy over rows with desired status codes.


For Each Sh In ThisWorkbook.Worksheets

      If InStr(1, Sh.Name, "-") > 0 Then

        LastInCurrSheet = Sh.Cells(1000, 2).End(xlUp).Row

        For i = 3 To LastInCurrSheet



        Select Case Cells(i, 2).Value
        Case vbNullString
        Case "GO TO LOAD", "SHIPMENT_DELIVERED", "SHIPMENT_DRY_VAN_CHARGE", "" 'Note nothing is done in this case
        Case Else
            Sh.Rows(i).Copy
            lastinTrackingbook = Trackingbook.Sheets("ALL DATA For Tracking").Cells(100000, 2).End(xlUp).Row
            Trackingbook.Sheets("ALL DATA For Tracking").Cells(lastinTrackingbook + 1, 1).PasteSpecial xlPasteValues
    End Select




'                If ((Cells(i, 2).Value = "SHIPMENT_DELIVERED") Or (Cells(i, 2).Value = "SHIPMENT_DRY_VAN_CHARGE")) Then
''do nothing
'                Else
'                Sh.Rows(i).Copy
'                lastinTrackingbook = Trackingbook.Sheets("ALL DATA For Tracking").Cells(100000, 2).End(xlUp).Row
'                Trackingbook.Sheets("ALL DATA For Tracking").Cells(lastinTrackingbook + 1, 1).PasteSpecial xlPasteValues
'                End If
                Next

End If
Next

  '''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''''

 ' this approach copies everything over, then deletes blank lines, and undesired status codes

'    For Each Sh In ThisWorkbook.Worksheets
'
'        If InStr(1, Sh.Name, "-") > 0 Then
'            lastinTrackingbook = Trackingbook.Sheets("ALL DATA For Tracking").Cells(100000, 2).End(xlUp).Row
'            LastInCurrSheet = Sh.Cells(1048576, 1).End(xlUp).Row
'
'            Sh.Range("A2:O" & LastInCurrSheet).Copy
'
'            Trackingbook.Sheets("ALL DATA For Tracking").Cells(lastinTrackingbook + 1, 1).PasteSpecial xlPasteValues
'
'        End If
'        lastinTrackingbook = ""
'        LastInCurrSheet = ""
'    Next
'
'    Trackingbook.Sheets("ALL DATA For Tracking").Activate



'This Removes Headers ( Except the first one, the actual header)

'    For i = 3 To lastinTrackingbook
'        If Cells(i, 1).Value = "Email" Then
'            Rows(i).Delete
'        End If
'
'    Next
'
'    MsgBox (lastinTrackingbook) ' this returns the correct row
'    For i = 2 To lastinTrackingbook
'
'        If Cells(i, 3) = "" Then
'If Cells(i, 3) = vbNullString Then
'
'            Rows(i).Delete
'        End If
'        If Cells(i, 2) = "SHIPMENT_DELIVERED" Then
'            Rows(i).Delete
'        End If
'    Next
'
'    Rows(1).Delete

    'Range("D2", "E" & lastinTrackingbook).NumberFormat = "MM-DD-YY"















    Application.ScreenUpdating = True
End Sub