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

Report
-
Posts
1291
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 5, 2021
-
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

1 reply

Posts
1291
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
May 5, 2021
229
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.

Subscribe To Our Newsletter!

The Best of CCM in Your Inbox

Subscribe To Our Newsletter!