Formula assistance

[theguy801]

Hi,
I am currently in search of a formula that will help me transfer a range of data to a new worksheet if a cell meets a specific criteria.
worksheet example:

A B C D E
1 STATUS YARD TIME ORIGIN PIECES
2 ARRIVED YES 22:05 DENVER 4500
3 COMPLETED YES 22:00 OHIO 2980
4 UNLOADING YES 10:08 L.A 7200
5 ENROUTE NO NA RENO 1200
6


What I am hoping to find is a formula that will sort a row onto a new worksheet depending on the text given in column A. in other words, if A2 says "arrived" move A2:M2 to A2:M2 in new worksheet.

Your assistance is greatly appreciated in advance.
Thank you

 

[Asheesh]

Welcome to the forum.

Are you looking for a formulae or VBA solution

 

[Asheesh]

VBA Solution

Check if the below helps

Option Explicit

Sub test()

Dim iwsh As Worksheet
Dim owsh As Worksheet
Dim uRange As Range

Set iwsh = Sheets("Sheet1") 'change the sheet name as need (Input Sheet Name)
Set owsh = Sheets("Sheet2") 'change the sheet name as need (Output sheet name)

iwsh.AutoFilterMode = False

iwsh.Range("A:M").AutoFilter Field:=1, Criteria1:="ARRIVED"

Set uRange = iwsh.UsedRange.SpecialCells(xlCellTypeVisible)

If (uRange.Rows.Count) > 1 Then
 
  uRange.Copy owsh.[A1]
 
End If

End Sub

btw, If you are looking for a formulae solution, We would first like to know the size of your input data table.