simple copy for stupid beginner

Posted by tony keane on January 21, 2001 8:55 AM

I am copying a lot of values from the cell above using the short-cut key "Control+'". But the keyboard positioning of "Control+'" is slowing me down. So I thought I would record a macro and assign it to a different key eg F12 which would be quicker, given my lack of typing skills. I need to use one hand only for typing as I need the other hand to finger down the list of values on the paper notes I am copying from so as not to lose my place and miss out a value. However, I found I had to face a problem with absolute versus relative ranges. Then I can't using the dialogue box to assign to any key other than Contol+letter and I cant find the VBA for the "Control+'" action. Can anyone help me put this effect onto the F12 key?

I have value in any cell. I cursor to the cell below it and press F12 and the value in the cell above is copied and the cursor moves down one cell. Pressing F12 again repeats the process. It seems simple! Thank you Microsoft! (I am using Excel 2000).

Posted by Tim Francis-Wright on January 22, 2001 2:43 PM

Excel's macro recorder is annoying that way.
However, it can do what you want.

First, in the ThisWorkbook object, put the
following code:

Private Sub Workbook_Open()
Application.OnKey "{F12}", "MyMacro"
End Sub

Private Sub Workbook_BeforeClose(Cancel As Boolean)
Application.OnKey "{F12}"
End Sub

Second, in a module, put the following code:
Sub MyMacro()
If ActiveCell.Row &LT;&GT; 1 Then
ActiveCell.Formula = ActiveCell.Offset(-1, 0).Formula
End If
ActiveCell.Offset(1, 0).Activate
End Sub

(There are probably more clever ways to write
this, but I know this works.)

Now, once you open the workbook in question,
hitting F12 will run MyMacro. Once you close
it, the workbook_close code will undo the assignment
of F12.

Good luck!

Posted by tony keane on January 26, 2001 2:36 AM

Tim, thank you for your kindness and help with the above query. I even managed to get it into Excel and working! My only complaint is that I didn't ask for help earlier. Thanks again.