PDA

View Full Version : I Need Eidt Code Copy And Paste



etheer
02-18-2014, 12:22 PM
Hi
I need help edit code work automatically everytime change cell A > 100000

copy and paste automatically


Sub oddinho2z()
x = 5
Range("A7:A166").Copy
Range("B7").Select
Do Until ActiveCell.Value = ""
ActiveCell.Offset(, 1).Select
x = x - 10
If x = 0 Then Exit Sub
Loop
ActiveCell.Select
ActiveSheet.Paste
End Sub

etheer
02-20-2014, 05:15 PM
please help me

westconn1
02-21-2014, 01:58 AM
put your code in the worksheet change event for the worksheet

i am not sure that i understand

everytime change cell A > 100000

try like

Private Sub Worksheet_Change(ByVal Target As Range)
if not intersect(target, range("a:a")) is nothing then
if target > 100000
application.enableevents = false
x = 5
Range("A7:A166").Copy
rw = 0
set tmp = Range("B7")
Do
rw = rw +1
x = x - 10
If x = 0 Then Exit Sub
Loop Until tmp.offset( ,rw).Value = ""
tmp.offset(,rw).Select
ActiveSheet.Paste
application.enableevents = true
end if
end if
end sub

snb
02-21-2014, 04:42 AM
Please wrap your VBA code in code tags !!

etheer
02-21-2014, 04:42 AM
Thank you westcom
erorr

Compile error
Synax error

westconn1
02-21-2014, 05:02 AM
Compile error
Synax erroron which line?

-rw should be rw in 2 places

x = x - 10
If x = 0 Then Exit Sub do not understand how x could ever = 0, it has a starting value of 5 then 5 -10 would be -5, every iteration after that would just be bigger minus value

etheer
02-21-2014, 06:02 AM
you can see file excelcopy and fix erorr

please help me westconn

westconn1
02-21-2014, 02:07 PM
change this line
If target > 100000 Then