VB to copy cell value in sheet1 to cells in sheet2 if criterion met

This following is just an example:

In sheet1 I have 4 columns; Name, Age, *** and Job.

Sheet2 has three columns; Name, Age and Job.

What I need is VB that will check the *** column from Sheet1 and when it is Male, the Name, Age and Job fields from that row are copied into the cells in Sheet2.

It's been a while since I've worked with VB and I am a bit rusty. Having a hard time figuring it out.

Originally Posted by Mumps1

Try:

Sub CopyData()
    Application.ScreenUpdating = False
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Range("C2:C" & LastRow)
        If rng = "Male" Then
            Range("A" & rng.Row & ":B" & rng.Row).Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            Range("D" & rng.Row).Copy Sheets("Sheet2").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

What is the best way to invoke it so that it doesn't copy itself over and over?

Originally Posted by Mumps1

Assuming that Sheet2 has headers in the first row, try:

Sub CopyData()
    Application.ScreenUpdating = False
    Sheets("Sheet2").UsedRange.Offset(1, 0).ClearContents
    Dim LastRow As Long
    LastRow = Cells.Find("*", SearchOrder:=xlByRows, SearchDirection:=xlPrevious).Row
    Dim rng As Range
    For Each rng In Range("C2:C" & LastRow)
        If rng = "Male" Then
            Range("A" & rng.Row & ":B" & rng.Row).Copy Sheets("Sheet2").Cells(Rows.Count, "A").End(xlUp).Offset(1, 0)
            Range("D" & rng.Row).Copy Sheets("Sheet2").Cells(Rows.Count, "C").End(xlUp).Offset(1, 0)
        End If
    Next rng
    Application.ScreenUpdating = True
End Sub

Works very well except when one of the cells is empty and then instead of leaving it empty on sheet2 it messes up the order