Sub CopyRange2()
Dim iLastRow As Long

With Sheets("Sheet1")
iLastRow = .Range("B8").End(xlDown).Row
If iLastRow 18 Then
iLastRow = 18
End If
.Range("B5:G5").Copy
.Range("B" & iLastRow + 1).PasteSpecial Paste:=xlValues
End With
Application.CutCopyMode = True

Range("A1").Select

End Sub


--

HTH

RP
(remove nothere from the email address if mailing direct)


"smandula" wrote in message
...
How do I stop copying, when the copy reaches B18:G18?
Here is the VBA that I use.

It copy's data from row B5:G5 to beginning line B8.
everytime the command copy button is pushed.
I want the copying to stop at row B18
then park the cursor everytime at A1

With Thanks
Steve

--------------------------------------------------------------------------
-------------

Sub CopyRange2()
Sheets("Sheet1").Range("B8").Select
'find your empty cell
Do Until ActiveCell.Formula = ""
ActiveCell.Offset(1, 0).Select
Loop
ActiveCell.Formula = Range("B5:G5").Copy
Selection.PasteSpecial Paste:=xlValues, Operation:=xlNone,
SkipBlanks:=
_
False, Transpose:=False
Application.CutCopyMode = True

'Stop copying when the copying reaches B18:G18

Range("A1").Select

End Sub

------------------------------------------------------------------