I have a large Excel worksheet with info sorted by date. The columns, from left to right, are "Date", "Origin", "Employee", "Vehicle #", "Product Count", and "Park Location."

What I'm looking for is a Macro to COPY the entire row from another workbook's "Main" sheet (on the server) based on "Vehicle #" and PASTE the row into a new worksheet in another workbook and name the new worksheet the "Vehicle #." Auto sort the data by date as well.

I've been using:


Sub Sorting()

Dim sh2 As Worksheet, finalrow As Long
Dim i As Long, lastrow As Long
Set sh2 = Sheets("160")
finalrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 1 To finalrow
If Cells(i, 1).Value = "160" Then
lastrow = sh2.Cells(Cells.Rows.Count, 1).End(xlUp).Row
Cells(i, 1).EntireRow.Copy Destination:=sh2.Cells(lastrow + 1, 1)

End If
Next i
End Sub

But this only works if I copy the "Main" sheet from the external workbook and paste it into the workbook I'm using. And I have to manually change the "Vehicle #."

Any/all assistance would be greatly appreciated.

Thank you