macro selecting row that cursor is on (not the same cell every tim

J

Jeremy

I need to create a macro to copy an entire row and paste as a transposed list
on a different tab. when recording the macro how can I make sure that in
future uses it will use the curson reference as a selection point, not the
absolute reference.

to clarify:
I want the user to be able to select A20 and have all the items in row 20
copied over. The next user may need row 32, so they will select A32 and use
the macro.

I know how the process to record, and it's a very easy macro, I just don't
want the macro to keep selecting the same row.
 
J

Jim Cone

Substitute "ActiveCell" for the cell reference used in the recorded macro...
ActiveCell.EntireRow.Copy
--
Jim Cone
Portland, Oregon USA
http://www.realezsites.com/bus/primitivesoftware
(Excel Add-ins / Excel Programming)



"Jeremy" <jeremiah.a.reynolds @ gmail.com>
wrote in message
I need to create a macro to copy an entire row and paste as a transposed list
on a different tab. when recording the macro how can I make sure that in
future uses it will use the curson reference as a selection point, not the
absolute reference.

to clarify:
I want the user to be able to select A20 and have all the items in row 20
copied over. The next user may need row 32, so they will select A32 and use
the macro.

I know how the process to record, and it's a very easy macro, I just don't
want the macro to keep selecting the same row.
 
G

Gary''s Student

Sub missive()
Set s2 = Sheets("Sheet2")
ActiveCell.EntireRow.Copy
s2.Range("B1").PasteSpecial Transpose:=True
End Sub

For test purposes, I used "Sheet2" as the name of the destination sheet.
 
G

Gary''s Student

A small change in the copy line:

Sub missive()
Set s2 = Sheets("Sheet2")
rw = ActiveCell.Row
Range("A" & rw & ":V" & rw).Copy
s2.Range("B1").PasteSpecial Transpose:=True
End Sub
 
J

Jeremy

That worked perfectly. One last question for this. I am usiny the code you
provided. How can I make it end on the Sheet2 page? Righht now it leaves
ends on Sheet1.
 
G

Gary''s Student

One additional line:

Sub missive()
Set s2 = Sheets("Sheet2")
rw = ActiveCell.Row
Range("A" & rw & ":V" & rw).Copy
s2.Range("B1").PasteSpecial Transpose:=True
s2.Activate
End Sub
 
Top