Copy Paste selected columns of a row to another sheet based on a cell value of the row

lakshi · May 20th 2020

Hi All,

I am trying to copy paste selected rows from sheet "Request" to sheet "Month end console". If "YES" is mentioned in J column of the row, that row should be transferred to "Month end console"

Below is the code I tried. This works correctly until a row is met which is not mentioned as "YES" in J column. Once "NO" row is met, the next immediate YES row is pasted with the previous NO row's data. Have attached the workbook herewith. Much appreciate kind support from you all!

Many thanks!

Code

Private Sub Worksheet_Change(ByVal Target As Range)
If Intersect(Target, Columns("J:J")) Is Nothing Then Exit Sub
If Target.Cells.Count > 1 Then Exit Sub
If Target.Value = "" Then Exit Sub
If Target.Value = "NO" Then Exit Sub
If Target.Value = "YES" Then Target.EntireRow.Copy Sheets("Month end console").Range("A" & Rows.Count) _
.End(xlUp).Offset(1, 0)
Target.Select
MsgBox "Data transfer complete!", vbExclamation
Sheets("Month end console").Select
End Sub

Display More

Logit · May 20th 2020

Code

Sub cpyPaste()

Dim i As Integer
   
For i = Cells(Rows.Count, "J").End(xlUp).Row To 2 Step -1
    If Sheets("Request").Range("J" & i).Value = "YES" Then
        Sheets("Request").Rows(i).Copy Sheets("Month end console").Range("A" & Rows.Count).End(xlUp).Offset(1)
    End If
Next i

End Sub

Display More

lakshi · May 21st 2020

Hi Logit,

Thank you for the support! The code shared by you copy paste rows with "NO" in column J also. Do you have any idea why is that?

Logit · May 21st 2020

I cannot reproduce the error here. However, paste the following macro into the SHEET MODULE. Evertime

the term YES is entered into COL J, the copy process will occur.

Code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Cells.Count > 1 Then Exit Sub
Application.EnableEvents = False
     If Not Intersect(Target, Columns("D")) Is Nothing Then
          If UCase(Target.Value) = "Y" Then
               Target.EntireRow.Copy Sheets("Sheet2").Cells(Rows.Count, 1).End(xlUp)(2) 'Edit sheet name
          End If
     End If
Application.EnableEvents = True
End Sub

Display More

lakshi · May 21st 2020

Hi Logit,

If copy paste cell has a value which is calculated with formula, then in the pasted sheet (month end module), that value swaps with the value in previous

"NO " row. Columns F, G, I, J have formulas hence pasted value gets changed in destination sheet it seems. Since columns A,B,C,D,E,H have no formulas it copy paste exact value (correct value).

Could you please check it and help me to copy paste only the value excluding the formulas.

Much thanks for the support!

Logit · May 21st 2020

It would be best for you to post a sample of your workbook (do not include confidential information) for download. That will allow

for an accurate examination and provide the best solution. Thanks.

lakshi · May 21st 2020

Please find the attached workbook.

Thank u very much for the support!

Logit · May 22nd 2020

.

Ok ... the following macro is functional via manual activation or auto-activation:

Code

Option Explicit

Sub CopyTerm()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long

Sheets("Month end console").Range("A2:J100").Value = ""

Lastrow = Sheets("Request").Cells(Rows.Count, "A").End(xlUp).Row
Dim ans As String
    
    For i = 2 To Lastrow
    ans = Sheets("Request").Cells(i, 10).Value
        
        If ans = "YES" Then

            Sheets("Request").Rows(i).Resize(, 10).Copy
            Sheets("Month end console").Rows(Sheets("Month end console").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial xlPasteValues
            Sheets("Month end console").Activate
            Sheets("Month end console").Range("A1").Select
            
        End If
        
    Next

Application.CutCopyMode = False
Sheets("Request").Activate
Sheets("Request").Range("A1").Select
Application.ScreenUpdating = True

Exit Sub

End Sub

Display More

With a COMMAND BUTTON you can manually activate the macro.

If you desire to auto-activate the macro, paste the following in to the Sheet Module :

Code

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)
    CopyTerm
End Sub

lakshi · May 22nd 2020

Hi Logit,

It works perfectly.

Thank you very very much for your help! I am a very new person to Excel VBA. I really appreciate the support.

This website and all the volunteers work here are really life savers!

Logit · May 22nd 2020

.

You are very welcome. Glad to assist.

Copy Paste selected columns of a row to another sheet based on a cell value of the row

Participate now!

Share

Tags