For Next, If Then?

[hviking]

Got an assignment that I'm having a hard time with.

I have a spreadsheet full of date, in random lenghts etc... The only thing stable on the sheet, is all the data I need has LOCATION03AB in column b on the lines I need.

I.E:

Ticket #, Location, Priority, Date/Time, User
001, LOCATION01CA, 3, 12:00pm, user1
016, LOCATION03AB, 3, 11:03pm, user9
019, LOCATION01CA, 3, 11:08pm, user11
103, LOCATION01CA, 4, 10:03am, user9
110, LOCATION03AB, 2, 07:00am, user3

etc..

what I need is a way to look through the spreadsheet. If it finds LOCATION03AB, it copies the line to a new line on a seperate tab, then goes to the next one. Once it finds another one, it copies it to the next line on the seperate tab. etc....

So on Sheet2, it would only display:

Ticket #, Location, Priority, Date/Time, User
016, LOCATION03AB, 3, 11:03pm, user9
110, LOCATION03AB, 2, 07:00am, user3



Basic layout:

DO until end of page
If b2="LOCATION03AB" then copy and paste, go to next line.
Next.

Hope the description is understandable.

 

[VoG]

Try

Sub atest()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("B" & i).Value = "LOCATION03AB" Then Rows(i).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
End Sub

 

[hviking]

Works exactly as I need it to.

Appricate it.

 

[hviking]

That part worked exactly as I was expecting, but it gave unexpected results.

It pulled all the ones with the correct location, but now I need to filter out users that are not to be included.

Would there be a way to look up the location, and if the user part is in a list on say sheet3, then copy the line, if not just skip it?

Or, to make it shorter, possible, instead of looking for the location, look for the list of users, and if a match copy it.

 

[VoG]

Which column is the user in in Sheet1 and Sheet3?

 

[hviking]

The list of users are on Sheet3 a1:a15 and they are in column E on Sheet1

 

[VoG]

Try

Sub atest()
Dim LR As Long, i As Long
LR = Range("B" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("B" & i).Value = "LOCATION03AB" Then
        If IsNumeric(Application.Match(Range("E" & i).Value, Sheets("Sheet3").Columns("A"), 0)) Then
            Rows(i).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
    End If
Next i
End Sub

 

[hviking]

Works perfectly. Appreciate the help.