G'day everyone

Everyday I generate multiple reports, of which the information is extracted
from a Citrix DB, I have automated most of the reports using 2003 and am
onto the final stage.

We have 15 or so Major clients that I manually put into a Linehaul Report
daily

The code below works a treat on most of what I require, but I now need to go
a few steps further to satisfy my criteria for the Sheet layout.

Sub Split_Data()

Dim SourceSheet As Worksheet
Dim DestinationSheet As Worksheet
Dim rng As Range
Dim rng2 As Range

With Application
.ScreenUpdating = False
.EnableEvents = False
End With

'Start of NSW

Sheets("NSW").Select

Set SourceSheet = Sheets("Data")
Set rng = SourceSheet.Range("A8:O" & Rows.Count)
Set DestinationSheet = Sheets("NSW")

SourceSheet.AutoFilterMode = False
rng.AutoFilter Field:=1, Criteria1:="=SYD"

SourceSheet.AutoFilter.Range.Copy
With DestinationSheet.Range("A5")
.PasteSpecial xlPasteValues
Application.CutCopyMode = False
.Select
End With

Range("A4:O50").Select
Selection.Sort Key1:=Range("A4:A50"), Order1:=xlAscending, Header:= _
xlYes, OrderCustom:=1, MatchCase:=False, Orientation:=xlTopToBottom,
_
DataOption1:=xlSortNormal

'End of NSW

step 1.

I need to change:

Set SourceSheet = Sheets("Data")
Set rng = SourceSheet.Range("A8:O" & Rows.Count)
Set DestinationSheet = Sheets("NSW")

SourceSheet.AutoFilterMode = False
rng.AutoFilter Field:=1, Criteria1:="=SYD"

to

Set SourceSheet = Sheets("Data")
Set rng = SourceSheet.Range("A8:O" & Rows.Count)
Set DestinationSheet = Sheets("Linehaul Report")

SourceSheet.AutoFilterMode = False
rng.AutoFilter Field:=10, Criteria1:="=FEDEX"

SourceSheet.AutoFilter.Range.Copy
With DestinationSheet.Range("A8")
.PasteSpecial xlPasteValues
Application.CutCopyMode = False
.Select
End With

which then brings us to step 2.

Step 2.

Field/Column 10 generally has multiple words in it eg "FEDEX / DEPOT/ Blah,
blah, etc....."
I need to use a wildcard like "=LIKE("FEDEX"*") to filter out only those
rows that contain just that.

Then go to the Linehaul Sheet, paste in the Name.Value(FEDEX) into A8
Then Paste.Value all the matching rows into A9 until there are no more.
Then Drop down 2 rows and set the focus on that cell.

Step 3.

Go back to the search again:

Set SourceSheet = Sheets("Data")
Set rng = SourceSheet.Range("A8:O" & Rows.Count)
Set DestinationSheet = Sheets("Linehaul Report")

SourceSheet.AutoFilterMode = False
rng.AutoFilter Field:=10, Criteria1:="=POST LOG"

Then go to the Linehaul Sheet, paste in the Name.Value(POST LOG) into the
last cell focus

And so on until all my 15 majors have been accounted for, and in saying
that, I probably need to include a comment like "No Movements" in the event
no match is found.

Which would still require the Name.Value pasted in the next available cell,
then in the next cell below that have it display the comment.

It's a big ask I know, so I won't be overly disappointed should this be
somewhat "pie in the sky"

Thx heaps in advance & I am sincerely looking forward to any
comments/suggestion anyone has to offer.

Regards
Mark.