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

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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!
 
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
 
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
 
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?
 
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
 
Thanks Tom, but I just get the error that "Object doesn't support this
property or method"...
 
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
 
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
 
Back
Top