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.