Macro by Reference

lecxE · Apr 10th, 2002, 10:27 AM

As an example of my dilemma, I simply want a Macro that when executed, copies the contents from the cell immediately above the active cell and moves one cell to the right. I record my macro in cell b2 and I get:

Range("B1").Select
Selection.Copy
Range("B2").Select
ActiveSheet.Paste
Range("C2").Select

Obviously, each time I execute the macro, I copy the same exact data from B1 to B2.

I know I can edit the macro to use ActiveCell.offset, but can I get it to work by reference while recording with keystrokes?

Thanks for any help you may offer.

AJ · Apr 10th, 2002, 10:35 AM

I'm pretty certain that you cannot do this by recording. Rather only by writing the code.
If you need help with the code, gi's a shout.

Cosmos75 · Apr 10th, 2002, 12:03 PM

This should work.

Sub CopyActiveCellAbove()

'Select Cell 1 row above active cell
'which will now be the active cell
ActiveCell.Offset(-1, 0).Select

'Copy Selected Cell
Selection.Copy

'Value of the cell 1 row below new active cell = new active cell
Selection.Offset(1, 0).Value = Selection

'Select cell 1 row down and 1 column to the right of new active cell
ActiveCell.Offset(1, 1).Select

End Sub

EDIT: Added comments to macro so that you can follow what I did.

[ This Message was edited by: cosmos75 on 2002-04-11 10:13 ]

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

Apr 10th, 2002, 10:27 AM	#1
lecxE Board Regular Join Date: Apr 2002 Posts: 106	As an example of my dilemma, I simply want a Macro that when executed, copies the contents from the cell immediately above the active cell and moves one cell to the right. I record my macro in cell b2 and I get: Range("B1").Select Selection.Copy Range("B2").Select ActiveSheet.Paste Range("C2").Select Obviously, each time I execute the macro, I copy the same exact data from B1 to B2. I know I can edit the macro to use ActiveCell.offset, but can I get it to work by reference while recording with keystrokes? Thanks for any help you may offer.

Apr 10th, 2002, 10:35 AM	#2
AJ Board Regular Join Date: Mar 2002 Location: =ActiveCell.Address Posts: 478	I'm pretty certain that you cannot do this by recording. Rather only by writing the code. If you need help with the code, gi's a shout.

Apr 10th, 2002, 12:03 PM	#3
Cosmos75 Board Regular Join Date: Feb 2002 Location: Tulsa, OK Posts: 354	This should work. Sub CopyActiveCellAbove() 'Select Cell 1 row above active cell 'which will now be the active cell ActiveCell.Offset(-1, 0).Select 'Copy Selected Cell Selection.Copy 'Value of the cell 1 row below new active cell = new active cell Selection.Offset(1, 0).Value = Selection 'Select cell 1 row down and 1 column to the right of new active cell ActiveCell.Offset(1, 1).Select End Sub EDIT: Added comments to macro so that you can follow what I did. [ This Message was edited by: cosmos75 on 2002-04-11 10:13 ]