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

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Jeremy - 19 May 2008 14:54 GMT

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.

Reply to this Message

Gary''s Student - 19 May 2008 15:10 GMT

Any specific destination on the different tab??

Signature

Gary''s Student - gsnu200787

> 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
[quoted text clipped - 8 lines]
> 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.

Reply to this Message

Jeremy - 19 May 2008 15:18 GMT

Yes, It will alsays paste into B1 (paste special as value with transpose
checked)

> Any specific destination on the different tab??
>
[quoted text clipped - 10 lines]
> > 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.

Reply to this Message

Gary''s Student - 19 May 2008 15:23 GMT

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.

Signature

Gary''s Student - gsnu200787

> Yes, It will alsays paste into B1 (paste special as value with transpose
> checked)
[quoted text clipped - 13 lines]
> > > 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.

Reply to this Message

Jeremy - 19 May 2008 15:25 GMT

What If I only need the data through Column V not the entire row?

> Sub missive()
> Set s2 = Sheets("Sheet2")
[quoted text clipped - 21 lines]
> > > > 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.

Reply to this Message

Gary''s Student - 19 May 2008 16:05 GMT

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

Signature

Gary''s Student - gsnu200787

> What If I only need the data through Column V not the entire row?
>
[quoted text clipped - 23 lines]
> > > > > 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.

Reply to this Message

Jeremy - 19 May 2008 16:15 GMT

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.

Reply to this Message

Gary''s Student - 19 May 2008 17:09 GMT

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

Signature

Gary''s Student - gsnu200787

> 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.

Reply to this Message

Jim Cone - 19 May 2008 15:17 GMT

Substitute "ActiveCell" for the cell reference used in the recorded macro...
ActiveCell.EntireRow.Copy

Signature

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.

Reply to this Message