Do Copy Paste on different Cell, when type "OK",Macro newbie, Help!

Hi All,

linkback : http://www.mrexcel.com/forum/excel-q...wbie-help.html

I Have this problem. I want to when type OK, it'll copy and paste it as value based on its row. and select other cell
like this pic below.

so when i type OK :
- on D5, it'll copy F5:H5, and paste to J5:L5, and then select cell N5
- on D6, it'll copy F6:H6, and paste to J6:L6, and then select cell N6
.
.
.
and so on until D14.

right now i'm using this code below on worksheet change, but it's too long, and kinda evaluate from first - end every time run the code... someone help me?

i've also upload the file http://s000.tinyupload.com/?file_id=...70715604698398

thanks a lot for the help
warm regards,

Adrian

Capture.JPG

Private Sub Worksheet_Change(ByVal Target As Range)

If Range("D5").Value = "OK" Then
    Application.EnableEvents = False
    Range("F5:H5").Copy
    
    Sheets("Sheet1").Range("J5:L5").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    Range("N5").Select
Else
        
End If

Application.EnableEvents = True

'-----------------------------------------------------------
If Range("D6").Value = "OK" Then
    Application.EnableEvents = False
    Range("F6:H6").Copy
    
    Sheets("Sheet1").Range("J6:L6").PasteSpecial xlPasteValues
    Application.CutCopyMode = False
    
    Range("N6").Select
Else
        
End If

Application.EnableEvents = True

End Sub

Originally Posted by Special-K

in J5
=IF(D5="OK",F5,"")
copy down and along as far as L14

Hi Special - K,

I want to prevent contain function, and automatically paste as value, and DavesExcel have just answered my question.

Originally Posted by davesexcel

Private Sub Worksheet_Change(ByVal Target As Range)
    If Target.Count > 1 Then Exit Sub    ' this stops code error if more than one cell is changed at once

    If Not Application.Intersect(Target, Me.Range("D5:D14")) Is Nothing Then    ' indicates the Target range

        If Target = "OK" Then

            Range(Cells(Target.Row, "J"), Cells(Target.Row, "L")).Value = Range(Cells(Target.Row, "F"), Cells(Target.Row, "H")).Value

        End If

    End If

End Sub

See here
http://www.excelforum.com/tips-and-t...et-events.html

Hi DavesExcel,

Thanks a lot for the code. it works perfectly. 1 request remained, how to automatically select cell in column N in row where i type OK?

warm regards,

Adrian