recording a macro

[paul mahnke]

I recorded a macro to copy the cell below to the current cell

when I run the macro from a different cell, it just goes back to the
original cell that I recorded the macro on, and copies the cell below
that cell up one.

How do I make the macro so it works on any cell that I may be currently
in?

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****

 

[John Wilson]

paul,

When a macro is recorded, it uses absolute references to ranges.
Your recorded macro probably looks something like this:

Sub Macro2()
Range("D7").Select
Selection.Copy
Range("D6").Select
ActiveSheet.Paste
End Sub

You can modify it to use the currently active cell this way
Sub Macro3()
ActiveCell.Offset(1, 0).Select
Selection.Copy
ActiveCell.Offset(-1, 0).Select
ActiveSheet.Paste
End Sub

but even that's not a good way to do it. You should rarely
have to select a cell to work with it. Try this one:

Sub Macro4()
ActiveCell.Offset(1, 0).Copy ActiveCell
End Sub

John

 

[paul mahnke]

thank you, John

***** Posted via: http://www.ozgrid.com
Excel Templates, Training & Add-ins.
Free Excel Forum http://www.ozgrid.com/forum *****