limiting pasted rows

[redirect]

Help on a solution would be greatly appreciated. The board has been very helpful to date and much appreciated.

I am filtering a list and copying it from one sheet to another. I am using a macro that finds the next empty row and pastes to it. However, I want to be able to limit the number of rows that can be pasted. For example, I may want to limit the macro to only pasting eight times. How do I add this to this macro? Thank you to anyone who can help. It could be a disaster if I allow too many rows to paste.

Public Sub copyitems()
''
'
Application.Goto Reference:="group1"
Selection.Copy
Sheets("Sheet2").Select
Sheets("Sheet2").Range("G639").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
Range("G639").Select

End Sub

 

[Dragracer]

Does this work for you?

<pre>
Public Sub copyitems()

Application.Goto Reference:="group1"
If Selection.Rows.Count > 8 Then
MsgBox " You can not copy more than 8 rows"
Exit Sub
End If
Selection.Copy
Sheets("Sheet2").Select
Sheets("Sheet2").Range("G65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

End Sub

</pre>

 

[redirect]

Thank you for the reply. I haven't been able to get it to work yet. You placed the If statement after:
Application.Goto Reference:="group1"
group1 is actually a filtered list so the message comes up every time. I am filtering the group1 list to a single item on one sheet and pasting a maximum number of 8 items with a button into:
Sheets("Sheet2").Select
Sheets("Sheet2").Range("G639").Select
Selection.End(xlUp).Select
ActiveCell.Offset(1, 0).Range("A1").Select
ActiveSheet.Paste
This goes to G639 and finds the next empty cell to paste into. I don't want to allow more than eight items selected and pasted. Where would I put the error message if I want to limit it to no more than eight selections pasted?

 

[Dragracer]

Give this one a try. Post back if it doesn't do what you want.

<pre>
Public Sub copyitems()

Range("group1").Select
If Application.WorksheetFunction.Subtotal(3, Range("group1").Columns(1).Rows) > 8 Then
MsgBox " You can not copy more than 8 rows"
Exit Sub
End If

Selection.Copy
Sheets("Sheet2").Select
Sheets("Sheet2").Range("G65536").End(xlUp).Offset(1, 0).Select
ActiveSheet.Paste

End Sub

</pre>