Here is how I thought of to handle it regardless of blank rows between load
info and grower name location. Is there some better way to do this? Any
built-in VBA options that would do this quicker and/or with less code?

Included sample includes the first part of the FindDrivers routine down to
and including the new coding I added above the code to actually copy the
contents from the source file to the destination file and page.

_______________

For k = 1 To 3 Step 2
Loc = Driver
Select Case k
Case Is = 1: Lotmask = "A?"
Case Is = 3: Lotmask = "B?"
End Select

With Worksheets(k)
("A1").Select
Set found = .Cells.Find(What:=Loc)
If Not found Is Nothing Then
Loc = found.Address
Do
Whr = found.Row
WhrFarm = Whr
Do
WhrFarm = WhrFarm - 1
FarmLoc = ActiveWorkbook.Worksheets(k).Cells(WhrFarm, 1)
Loop Until FarmLoc Like Lotmask Or WhrFarm < 10


"rcmodelr" wrote:

Your revision might work MOST of the time, but usually with the first lot of
Day shift, and Last lot of Night shift...

The sheet is setup to allow night shift to run all their loads plus a few
extra loads if Day shift starts at the same farm.
The Day shift sheet is set to allow ALL the scheduled Day shift loads from
the first farm, PLUS a couple extra rows if night shift caught at the same
farm.

If the last load of night shift comes in only 1/2 full, but day shift starts
before all night shift's loads were run, normally the loads on the night
shift sheet will be in arrival sequence order, and any FULL loads night shift
left and the load being unloaded at shift change will end up getting copied
to the top of the Day shift sheet, and just deleted from the night shift
sheet resulting in 1 or more BLANK rows between the last load of night shift,
and the second-to-last load of night shift, with the Grower name somewhere
above the second-to-last night shift load row.

And when Day shift loads start coming in, often, the weighmaster will have
had the sheet set up with a couple extra rows for night shift carry-over. If
it looks like night shift might not finish their loads, the weighmaster will
often start entering the Day shift load data 1 to 3 rows BELOW the grower
name for that first lot.

Most reliable way to check for the existence of the Grower's name (since the
coding has no way of knowing the grower name in advance) would be to, before
assuming the grower name is in that row, check the A column, which contains
formula to calculate driver turn-around time. If the row being checked has
the Grower Name, the A column would have the LOT NUMBER.

Lot Number is ALWAYS only 2 characters in size. Night shift's lot numbers
as A1, A2 A3, etc... Day shift lot numbers as B1, B2, B3, etc

So to check for existence of grower's name in the row, best bet would be to
check for a lot number with a lot number mask variable set to check for a lot
number like "A?" for night shift lot sheet, and Like "B?" for the Day shift
sheet.

But how do I get that checked WHILE ALLOWING FOR any BLANK rows between the
row where the driver name was found and the row containing the Lot number and
Grower Name (Lot Number in Column A, Grower Name in column D, which is the
SAME column that contains the ticket number in the sheet this other program
is gathering its info for the individual Driver Recap pages?