Copy based on condition

[srosk]

Hi there everyone. Hopefully an easy one


I have the following code which I am using on another part of my macro (which might be helpful.. I think).

Ultimately what I need to do, is copy columns A:J for any row where column L equals Not Exported. It will copy to sheet WS2, beginning on row 8. On WS2, using columns B:K (as opposed to A:J). Once copied, update value in column L with value of Exported

Is this possible in the slightest?

Function CopyToRPSTEXT()

Application.ScreenUpdating = False
Dim WS1 As Worksheet, WS2 As Worksheet, lr As Long


Set WS1 = Worksheets("2")
Set WS2 = Worksheets("3")
lr = WS1.Cells(Rows.Count, "B").End(xlUp).Row


WS1.Range("A2:J" & lr).Copy
WS2.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.ScreenUpdating = True
Application.CutCopyMode = False


End Function

 

[JoeMo]

Sub srosk()
Dim WS1 As Worksheet, WS2 As Worksheet, lr1 As Long, lr2 As Long, c As Range
Application.ScreenUpdating = False
Set WS1 = Worksheets("2")
Set WS2 = Worksheets("3")
lr1 = WS1.Cells(Rows.Count, "L").End(xlUp).Row
lr2 = 8
For Each c In WS1.Range("L1:L" & lr1)
    If c.Value = "Not Exported" Then
        WS1.Range(WS1.Cells(c.Row, "A"), WS1.Cells(c.Row, "J")).Copy
        WS2.Cells(lr2, "B").PasteSpecial Paste:=xlPasteValues
        lr2 = WS2.Cells(WS2.Rows.Count, "B").End(xlUp).Row + 1
        c.Value = "Exported"
    End If
Next c
Application.ScreenUpdating = True
Application.CutCopyMode = False
End Sub