VBA help: Using user's selection to copy entire row

[JAnderson]

Greetings all,

I have two large spreadsheets, let's call them "Account" and "Toy Store". I
want the user to run a macro that copies the entire row(s) of highlighted
cells and places it at the bottom of a running total on the next sheet. For
example:

"Account" sheet:

A B C D
1/1/07 Smith $342 $700
1/7/07 Jones $342 $855
2/1/07 Anders $342 $855
2/12/07 Johnson $342 $700

Say the user highlights the $855 cells (D2 and D3 in this example). I want
the macro to take that selection, copy the entire row, and paste the row at
the bottom of the "Toy Store" worksheet (ideally leaving room for a few rows
of totals).

How can I make the Selection property return the row number(s), and how can
I make sure it pastes at the bottom of the other sheet? Thanks so much!

 

[Charles Chickering]

You don't really need to intercept what rows are selected:
With Worksheets("Toy Store")
Selection.EntireRow.Copy _
.Range("A" & .Rows.Count).End(xlUp).Offset(1)
End With

 

[JAnderson]

Hmm. I just get an error on the first line saying Subscript out of Range.
Is this also supposed to insert the rows as well?

Thanks,

Jason

 

[JAnderson]

Sorry, the error I am getting is "Object not defined" and on the third line
of code.

 

[JAnderson]

After messing around for a bit, I came up with this which works perfectly
(even though it's not that elegant):

Sub Macro1()
Selection.EntireRow.Copy
Sheets("Toy Store").Select
With ActiveSheet
StopRow = .Range("A" & .Rows.Count).End(xlUp).Row
StopRow = StopRow + 1
Rows(StopRow).Insert Shift:=xlDown
End With
End Sub

Is there a way I can incorporate the offset function to avoid having to
increment the variable by 1 each time?

 

[Tom Ogilvy]

Sub Macro1()
Selection.EntireRow.Copy
With Sheets("Toy Store")
.Rows( .Range("A" & .Rows.Count).End(xlUp)(2).row)
.Insert Shift:=xlDown
End With
End Sub

although I don't know why you need to insert. You could just do

Sub Macro1()
With Sheets("Toy Store")
Selection.EntireRow.Copy
.Rows( .Range("A" & .Rows.Count).End(xlUp)(2).row)
End With
End Sub

 

[JAnderson]

Thanks Tom, but I just get the error that "Object doesn't support this
property or method"...

 

[JLGWhiz]

If you use this one and put everything between With and End With on
one line, it will work.

Sub Macro1()
Selection.EntireRow.Copy
With Sheets("Toy Store")
.Rows( .Range("A" & .Rows.Count).End(xlUp)(2).row)
.Insert Shift:=xlDown 'Move up to end of previous line
End With
End Sub

 

[Tom Ogilvy]

Sub Macro1()
Selection.EntireRow.Copy
With Sheets("Toy Store")
.Rows( .Range("A" & .Rows.Count) _
.End(xlUp)(2).row) _
.Insert Shift:=xlDown
End With
End Sub