Move down one line

[Guest]

Sub Test()
Workbooks.Open Filename:= MyPath\myfile.xls
Windows("File1.xls").Activate
Range("BP18:BU18").Select
Selection.Copy
Windows("myfile").Activate
Range("Q1").Select
Selection.End(xlDown) (2, 1).Select
Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
:=False, Transpose:=False
ActiveWorkbook.Save
ActiveWorkbook.Close

End Sub

This code is run daily and copies BP18:BU18 to a summary sheet. The first
run should copy to Q9 the next day should be Q10 etc.
How do I modify "Selection.End(xlDown) (2, 1).Select" to move down one line
each time this code runs? Or should I be using something different to
accomplish this?
Thanks
Sandy

 

[Tom Ogilvy]

Sub Test()
Dim MyPath as String
Dim bk as Workbook, sh as Worksheet, rng as Range
MyPath = "C:\MyFolder"
set bk = Workbooks.Open( Filename:= MyPath & "\myfile.xls")
set sh = Workbooks("File1.xls").Activesheet
set rng = Workbooks("myfile.xls").Worksheets(1).Range("Q9")
if not isempty( rng) then
set rng = rng.Parent.Cells(rows.count,"Q").End(xlup)(2)
End if
sh.Range("BP18:BU18").Copy
rng.PasteSpecial Paste:=xlPasteValues, _
Operation:=xlNone, _
SkipBlanks:=False, _
Transpose:=False
bk.Close SaveChanges:=True
End Sub