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

[radian89]

Hi All,

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

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 here http://s000.tinyupload.com/?file_id=34348770715604698398

thanks a lot for the help
warm regards,

Adrian

[/IMG]

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

 

[vcoolio]

Hello Radian,

The code that Davesexcel supplied does the job. If all you want is to add a line to select corresponding cells in Column N, then add the following line:-

Cells(Target.Row, "N").Select

just after this line:-

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

Cheerio,
vcoolio.

 

[radian89]

Hello Radian,

The code that Davesexcel supplied does the job. If all you want is to add a line to select corresponding cells in Column N, then add the following line:-

Cells(Target.Row, "N").Select

just after this line:-

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

Cheerio,
vcoolio.

Hi vcoolio,
And it works... woohooo...


thanks a lot...
warm regards

Adrian

 

[vcoolio]

Excellent Adrian! Glad that I was able to help out.

Cheers to Davesexcel also.

Cheerio,
vcoolio.