VBA Conditional copy/paste

[BobK289]

Hello, would someone care to give me some guidance on this? I need a macro to copy most of a row of data from one sheet to a second sheet depending on condition being met in one of the cells. From sheet "New 2010", beginning in A4, I'd like to copy data A:AB for every row that has an "X" in cell AL. I'd like to Paste Special Values into sheet "Open" starting in row 20. The end result will be ~50 rows of data out of a couple thousand lines from "New 2010". I've written simple macros before, but this one (even with the help of examples I've found) is a little beyond me.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-comfficeffice" /><o></o>
<o></o>
Any help would be appreciated.<o></o>
<o></o>
Thanks. <o></o>

 

[lenze]

Hi and welcome to the board!!!
Untested, but give this a shot!!

Sub CopyOnCondition()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim LR1 As Long
Dim LR2 As Long
Dim cl As Range
Set ws1 = Sheets("New 2010")
Set ws2 = Sheets("Open")
LR = ws1.Cells(Rows.Count, "AL").End(xlUp).Row
For i = 4 To LR1
  Set cl = ws1.Cells(i, "AL")
     If cl = "X" Then
        ws1.Cells(cl.Row, "A").Resize(1, 28).Copy
        LR2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row
        ws2.Cells(LR2 + 1, "A").PasteSpecial Paste:=xlPasteValues
     End If
Next i
End Sub

lenze

 

[VoG]

Try

Sub Cpy()
Dim LR As Long, i As Long, Done As Boolean
With Sheets("New 2010")
    LR = .Range("A" & Rows.Count).End(xlUp).Row
    For i = 4 To LR
        If .Range("AL" & i).Value = "X" Then
            .Range("A" & i).Resize(, 28).Copy
            If Done Then
                Sheets("Open").Range("A" & Rows.Count).End(xlUp).Offset(1).PasteSpecial Paste:=xlPasteValues
            Else
                Sheets("Open").Range("A20").PasteSpecial Paste:=xlPasteValues
                Done = True
            End If
        End If
    Next i
End With
End Sub

 

[BobK289]

Thanks lenze and VoG.

lenze, I couldn't get your code to run but VoG's did the trick.

Thank you both again, now I need to spend some time learning how the code works.

Bob

 

[lenze]

Sorry!! As I said, I didn't test it. This should also work

Sub CopyOnCondition()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim LR1 As Long
Dim LR2 As Long
Dim cl As Range
Set ws1 = Sheets("New 2010")
Set ws2 = Sheets("Open")
LR1 = ws1.Cells(Rows.Count, "A").End(xlUp).Row
For i = 4 To LR1
    If UCase(ws1.Cells(i, "AL")) = "X" Then
        ws1.Cells(i, "A").Resize(, 28).Copy
        LR2 = ws2.Cells(Rows.Count, "A").End(xlUp).Row
        ws2.Cells(LR2 + 1, "A").PasteSpecial Paste:=xlPasteValues
    End If
Next i
End Sub

lenze

 

[BobK289]

Awesome, thank you Lenze.