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

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!

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

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
--
Charles Chickering

"A good example is twice the value of good advice."


"JAnderson" wrote:

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!

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

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

"Charles Chickering" wrote:

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
--
Charles Chickering

"A good example is twice the value of good advice."


"JAnderson" wrote:

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!

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

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

"JAnderson" wrote:

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

"Charles Chickering" wrote:

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
--
Charles Chickering

"A good example is twice the value of good advice."


"JAnderson" wrote:

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!

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

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?


"JAnderson" wrote:

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!

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

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

--
Regards,
Tom Ogilvy



"JAnderson" wrote in message
...
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?


"JAnderson" wrote:

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!

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

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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy



"JAnderson" wrote in message
...
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?


"JAnderson" wrote:

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!

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

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

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

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


--
Regards,
Tom Ogilvy


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

"Tom Ogilvy" wrote:

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

--
Regards,
Tom Ogilvy



"JAnderson" wrote in message
...
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?


"JAnderson" wrote:

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!