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

  • 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!


  • 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.


  • 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!

  • .

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



    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
    1. Option Explicit
    2. Private Sub Worksheet_Change(ByVal Target As Range)
    3. CopyTerm
    4. End Sub
  • 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!