Copying data from 1 sheet to another if meets condition

[GabeKnight]

Hi,
I am using Excel 2003

Essentially we have a spreadsheet in which if column K displays "Yes" I would like the info in column H to be displayed in another sheet which will be used as a summary sheet.
There are around 278 rows and column K can either be a "Yes" or a "No" depending on conditions.

In the new sheet ("Action Plan") I only want the information from the rows with "Yes" displayed, so if there is 3 Yes's, only 3 lines appear, if 5 Yes's, 5 lines appear, etc.

I understand I'll need to use a macro to add new lines to the summary sheet as it searches through the data for "Yes" but am unsure how to do this as I am new to macros.

Any help would be greatly appreciated.
Many thanks

Tim

 

[VoG]

Try like this - change Sheet1 to suit

Sub cpy()
Dim LR As Long, i As Long
With Sheets("Sheet1")
    LR = .Range("K" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If .Range("K" & i).Value = "Yes" Then .Rows(i).Copy Destination:=Sheets("Action Plan").Range("A" & Rows.Count).End(xlUp).Offset(1)
    Next i
End With
End Sub

 

[GabeKnight]

Cheers for the quick response.....

It seems to almost do what I want. You can see the info scrolling through but ends up only displaying the last row.

Is there a way to display all rows that have "Yes"

Thanks
Tim

 

[VoG]

I just tested to make sure and it copied all rows with Yes in column K.

Where did you put the code. It has to go in a regular module.

 

[VoG]

Also, is there data in column A? If not what is the leftmost column with data?

 

[GabeKnight]

The data is in columns B through K, although A is a header so is merged through various rows.
Sorry if that makes it more awkward.

 

[VoG]

Try this

Sub cpy()
Dim LR As Long, i As Long
With Sheets("Sheet1")
    LR = .Range("K" & Rows.Count).End(xlUp).Row
    For i = 1 To LR
        If .Range("K" & i).Value = "Yes" Then .Rows(i).Copy Destination:=Sheets("Action Plan").Range("B" & Rows.Count).End(xlUp).Offset(1, -1)
    Next i
End With
End Sub

 

[GabeKnight]

Cheers mate.....
With slight tinkering have got it to work.

Many thanks for your help and quick response