MrExcel Publishing
Your One Stop for Excel Tips & Solutions

Macro Recording


Posted by Ulhas Menon on July 23, 2001 5:34 AM

I am trying to record a macro the subject matter being - I am trying to have a text or subject that is preprinted on some other cell to paste to a cell where the cursor rests at the time of hitting the macro but the macro records gives the cell reference as "a1" or "a2" where the cell was at the time of recording the macro can someone tell me how do i change this to the cell where the curser is at the time when i want to record the macro.


Posted by Bob on July 23, 2001 6:35 AM

You need to edit your macro after recording it and make a few changes. See below and note the use of a variable to store the address of the cell you were on.

Sub CopyAndPaste()
'
MyCell = ActiveCell.Address
' Find out where the user was before we run the macro.
'
Range("B15").Select
' Or Whatever cell you want to copy.
Selection.Copy
Range("MyCell").Select
ActiveSheet.Paste
End Sub


Posted by Barrie Davidson on July 23, 2001 10:22 AM

Hi Ulhas, I copied this directly from Excel's help.

"If you select cells while running a macro, the macro will select the same cells regardless of which cell is first selected because it records absolute cell references. If you want a macro to select cells regardless of the position of the active cell when you run the macro, set the macro recorder to record relative cell references. On the Stop Recording toolbar, click Relative Reference (this is an icon). Microsoft Excel will continue to record macros with relative references until you quit Microsoft Excel or until you click Relative Reference (the icon)again."

To access this, type in "Record a macro" in the search help input box.

Regards,
Barrie