Selective copying macro

[jon321]

Hey Im completely stuck, I need to do the following,

If any cell in a range contains the word "TRUE" then the macro copies 4 cells in that row and paste special "values" on sheet 2 starting at A1 and then moving to A2 if there is something already in A1.

Hope someone can help!!

 

[VoG]

Which range and which 4 cells should be copied?

 

[jon321]

the range is "b2:E10" and the 4 cells on the row within the range eg.

B2,C2,D2,E2 If true was found on this row.

 

[VoG]

Try

Sub cpy()
Dim i As Integer
With Sheets("Sheet1")
    For i = 2 To 10
        If WorksheetFunction.CountIf(.Range("B" & i).Resize(, 4), True) > 0 Then
            .Range("B" & i).Resize(, 4).Copy Destination:=Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1)
        End If
    Next i
End With
End Sub

 

[jon321]

Works to some extent - but it doesnt do "paste special -values" and starts in cell a2 on sheet 2???

 

[VoG]

Sub cpy()
Dim i As Integer
With Sheets("Sheet1")
    For i = 2 To 10
        If WorksheetFunction.CountIf(.Range("B" & i).Resize(, 4), True) > 0 Then
            .Range("B" & i).Resize(, 4).Copy
            Sheets("Sheet2").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
        End If
    Next i
End With
On Error Resume Next
With Sheets("Sheets2").Columns("A").SpecialCells(xlCellTypeBlanks).EntireRow.Delete
On Error GoTo 0
End Sub

Take it or leave it.