Program issue w/ VBA on next row

E

Erin

I have taken VB in college and used it some but I have not
touched it in almost 4 years. I need help, I did a
recording macro where I copied from E6-M6 pasted in E9-M9
and wanted it go to go the next line automatically and
saved. Well when I run the macro it just stays in the
same place. I want the macro to copy the formula and when
someone uses it and presses ctrl+k it automatically enters
in the information and goes to the next line. Can someone
help!?!?!?!

This is what I have so far and I have tried other things
but it still doesn't work.

' Keyboard Shortcut: Ctrl+k

Range("E6:M6").Select
Selection.Copy
Range("E9").Select
ActiveSheet.Paste
Application.CutCopyMode = False
Range("E10").Select
ActiveWorkbook.Save

End Sub

This is being used so people can enter in on one row at a
time information and when finished entering in the one row
they press ctrl+k and the macro does the rest and moves to
the next line and when the person is ready to enter in
another row the macro can do that.

Thanks!
 
T

Tom Ogilvy

' Keyboard Shortcut: Ctrl+k

Range("E6:M6").Select
Selection.Copy
Range("E9").Select
if not isempty(Range("E9")) then
if isempty(Range("E10")) then
Range("E10").Select
else
Range("E9").End(xldown)(2).Select
End if
End if
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.Save
End Sub
 
N

Nigel

Try replacing all of your code with the following......
It assumes that the last row of column E is last copy-paste your macro made,
and that you always want to copy range E6 to M6 to the next empty row.

' Keyboard Shortcut: Ctrl+k

Dim lastrow As Long
lastrow = Cells(Rows.Count, "E").End(xlUp).Row
Range("E6:M6").Copy Destination:=Range("E" & lastrow + 1)
ActiveWorkbook.Save

End Sub

Cheers
Nigel
 
Top