copy entire row based on value in cell

[stilgar]

hi,

i have a excel sheet with a large list of jobs etc on it and what i want is a macro which can be run from a control button, to check column "K" for the persons id, say "CS" or "DC". every person has a 2 letter ID.

then say CS is entered in the macro as the ID to search for, the marco will seach column k for all the cells containg CS and then copy the entire roz to a new sheet.

so say row 3, 5, and 7 have CS in column "K" then the entire rows 3, 5, and 7 and copied to sheet 2

there is only columns up to "O"

i'm getting better at macro's but still very much learning

any help would be great thanks

 

[VoG]

Try

Sub CopyID()
Dim LR As Long, i As Long, ID As String
ID = InputBox("Enter ID")
LR = Range("K" & Rows.Count).End(xlUp).Row
For i = 1 To LR
    If Range("K" & i).Value = ID Then Rows(i).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
Next i
End Sub

 

[littleiitin]

or you can use filters to do it

Sub CopyPaste()
    Sheet1.Columns("K:K").AutoFilter Field:=1, Criteria1:="CS"
    Sheet1.Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row).EntireRow.Copy
    Sheet2.Range("A" & Sheet2.Range("K" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteAll
End Sub

 

[stilgar]

the filters way works great but that mean's i would have to have a button for each person.

i like the first option but it only copies one row and the data isn't the same that it copies.

UPDATE:

sorry that data is the same but it only copies the last row. it doesn't copy all the rows that have "cs" for example in column "K"

 

[VoG]

i like the first option but it only copies one row and the data isn't the same that it copies.

For me it copies all rows that match exactly.

 

[littleiitin]

Sub CopyPaste()
    Dim ID As String
    ID = InputBox("Enter ID")
    Sheet1.Columns("K:K").AutoFilter Field:=1, Criteria1:=ID
    Sheet1.Range("K2:K" & Range("K" & Rows.Count).End(xlUp).Row).EntireRow.Copy
    Sheet2.Range("A" & Sheet2.Range("K" & Rows.Count).End(xlUp).Row + 1).PasteSpecial xlPasteAll
    Application.CutCopyMode = False
    
End Sub

 

[stilgar]

yeah that works well.

thanks for your help ppl!