Macro Copy Paste from one to another based on cell value Closed

Question

Hello,

I have two sheets. Phase I and Phase II. Phase I I have columns A-J. Im looking for a macro which would copy columns A-F and paste it to sheet Phase II A5 when value of column J on sheet Phase I is yes. 


Sub Yes1()
'Copy from one sheet to another based on cell value
Application.ScreenUpdating = False
Dim i As Integer
Dim lastRow As Long
Dim LastroWB As Long
lastRow = Cells(Rows.Count, "J").End(xlUp).Row + 1
LastroWB = Sheets("Phase II").Cells(Rows.Count, "A").End(xlUp).Row + 1

For i = 2 To lastRow

If Cells(i, 10).Value = "YES" Then
Range(Cells(i, 1), Cells(i, 6)).Copy Sheets("Phase II").Rows(LastroWB)
LastroWB = LastroWB + 1
End If
Next
Application.CutCopyMode = False
Application.ScreenUpdating = True
End Sub

vcoolio · Answer

Hello DPD,

Your code appears to work just fine but if you are not happy with it, I've refined it a little for you as follows:-

Sub Yes1()

Application.ScreenUpdating = False

Dim i As Integer
        Dim LastRow As Long
        
LastRow = Cells(Rows.Count, "J").End(xlUp).Row

For i = 2 To LastRow
        If UCase(Cells(i, 10).Value) = UCase("YES") Then
        Range(Cells(i, 1), Cells(i, 6)).Copy Sheet2.Range("A" & Rows.Count).End(3)(2)
        End If
    Next
    
Application.CutCopyMode = False
Application.ScreenUpdating = True
Sheet2.Select

End Sub

I've altered line 11 a little just in case other users don't spell "YES" as you would like them to. So, "YES", "Yes" or "yes" should all work.

You can also change the sheet reference to Sheets("Phase II") if you prefer. I generally prefer to use the actual sheet code in a macro (I'm assuming that your sheet "Phase II" is Sheet2).

I hope that this helps.

Cheerio,
vcoolio.

Macro Copy Paste from one to another based on cell value

1 response

Similar discussions

Subscribe To Our Newsletter!