Need to be able to move things in order based on sequence of events.

[castlefamily]

So I come to the Gurus again needing help. I am attaching the workbook so that you can see what I am doing. I am responsible for rail car inspections. I have been able with help to write a macro that allows me to put an X in column J and that move those cars and their info to the inspection sheet. The problem that I am having is this: the cars come into the yard in a certain order. Meaning that when I inspect them the sheet has to stay in the order that the arrive. Right now, when I put the X it automatically puts them in order by date, and that isn't usable for my work. Is there a way that the order I put the X in will be the order that they show up on the inspection screen?

Here is the current code:

Sub Transfer()
    Dim wshS As Worksheet
    Dim wshT As Worksheet
    Dim rng As Range
    Dim strAddress As String
    Dim s As Long
    Dim t As Long
    Application.ScreenUpdating = False
    Set wshT = Worksheets("Inspection")
    t = wshT.Range("B" & wshT.Rows.Count).End(xlUp).Row
    If t < 4 Then t = 4
    For Each wshS In Worksheets
        If wshS.Name <> wshT.Name Then
            Set rng = wshS.Range("J:J").Find(What:="1", LookAt:=xlWhole)
            If Not rng Is Nothing Then
                strAddress = rng.Address
                Do
                    t = t + 1
                    s = rng.Row
                    wshS.Range("A" & s & ",B" & s).Copy _
                        Destination:=wshT.Range("B" & t)
                    wshS.Range("F" & s).Copy _
                        Destination:=wshT.Range("D" & t)
                    wshS.Range("D" & s).Copy _
                        Destination:=wshT.Range("E" & t)
                    
                    Set rng = wshS.Range("J:J").FindNext(After:=rng)
                    If rng Is Nothing Then Exit Do
                Loop Until rng.Address = strAddress
            End If
        End If
    Next wshS
    Application.CutCopyMode = False
    Application.ScreenUpdating = True
End Sub

Also the workbook is attached and has been approved by my company to be posted as it does not contain sensitive material.
Thank you all for the help.
Dustin

[Paul_Hossler]

I added CODE tags to your post - use the [#] icon to enter them and paste your macro between. It sets the macro off and does some formatting

Instead of your Transfer() macro, I'd use a WS event so that when I put an X in col J on the other sheets, the information would automatically go to the next row on Inspection

Option Explicit




Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    Dim rNextEntry As Range, rCell As Range
    Dim wsInsp As Worksheet, ws As Worksheet
    
    'we only want the RAIL CAR sheets
    If Sh.Range("A2").Value <> "RAIL CAR" Then Exit Sub
    
    'just use the first cell
    Set rCell = Target.Cells(1, 1)
    
    'if not in col J
    If rCell.Column <> 10 Then Exit Sub
    
    'if cell to left is empty then exit
    If Len(rCell.Offset(0, -1).Value) = 0 Then Exit Sub
    
    'x or X
    If UCase(rCell.Value) <> "X" Then Exit Sub
    
    'get first blank cell in Col B on Inspection sheet
    Set wsInsp = Worksheets("Inspection")
    Set rNextEntry = wsInsp.Cells(wsInsp.Rows.Count, 2).End(xlUp).Offset(1, 0).EntireRow


    Application.EnableEvents = False
    With rCell.EntireRow
        rNextEntry.Cells(1, 2).Value = .Cells(1, 1).Value       '   PREFIX --> INT
        rNextEntry.Cells(1, 3).Value = .Cells(1, 2).Value       '   CAR NUMBER
        rNextEntry.Cells(1, 4).Value = .Cells(1, 5).Value       '   MATERIAL
        rNextEntry.Cells(1, 6).Value = .Cells(1, 4).Value       '   POINT --> SHIPPER
    End With
    Application.EnableEvents = True


    wsInsp.Select
 
 End Sub

[castlefamily]

It works amazing. I have to shift the columns over as the code right now puts the oracle number in shipper column instead of the shipper and the material is in the status column, but I think with what you built, I should be able to make those corrections. Thank you for the fast and helpful response sir.

[Paul_Hossler]

1. Glad

2. Sorry

3. I never did figure out what your other macros (e.g. Coke1, Coke2, ...) were supposed to do or even if they're still needed. They are almost identical, except for the 'Find What'

[castlefamily]

I was trying to see if I split it by number if that would allow it in the order I was wanting. Thinking if I numbered them and brought them in with different macros it would allow me to order them better. It failed horribly, which is why I came here for help.