Copy & paste simple problem
 

Hi,
I would appreciate some help with this macro as I am still grabbling with these.
I have recorded the Macro below.

My problem is, pasting.
The line - Selection.End (xlDown)).Select
stops on the last filled in row, and a Paste will
overwrite that last cell.
So I recorded a down arrow move and the code selects that cell col and Row,
Range ("A129").select, but next copy and paste needs to append data onwards
from that last row.

How do I code it to jump down one row when I do the Selection.End (xlDown)?

Thanks In Advance
Cheers Paul

Sub Copyfive()
'
' Copyfive Macro
''
Windows("Source.xls").Activate
Range("A9").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Destination.xls").Activate
Range("A9").Select
Selection.End(xlDown).Select
Range("A129").Select
ActiveSheet.Paste
End Sub

Copy & paste simple problem
 

Hi Paul,

You just need Offset(1,0) to go one row down in same column. To avoid going
to an empty cell above the bottom cell, try something like this:

Sub test()
Dim r1 As Range, r2 As Range
Set r1 = ActiveCell

Set r2 = Cells(65536, r1.Column).End(xlUp).Offset(1, 0)
MsgBox r2.Address
r2 = "hello"
'r2.select ' probably not necessary
End Sub

Regards
Peter


"Paul" wrote in message
om...
Hi,
I would appreciate some help with this macro as I am still grabbling with
these.
I have recorded the Macro below.

My problem is, pasting.
The line - Selection.End (xlDown)).Select
stops on the last filled in row, and a Paste will
overwrite that last cell.
So I recorded a down arrow move and the code selects that cell col and
Row,
Range ("A129").select, but next copy and paste needs to append data
onwards
from that last row.

How do I code it to jump down one row when I do the Selection.End
(xlDown)?

Thanks In Advance
Cheers Paul

Sub Copyfive()
'
' Copyfive Macro
''
Windows("Source.xls").Activate
Range("A9").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Destination.xls").Activate
Range("A9").Select
Selection.End(xlDown).Select
Range("A129").Select
ActiveSheet.Paste
End Sub

Copy & paste simple problem
 

Thanks very much Peter.
Works very well.
Sorry for the late response as I have not been able to connect to
internet for some weeks.
Cheers Paul


"Peter T" <peter_t@discussions wrote in message ...
Hi Paul,

You just need Offset(1,0) to go one row down in same column. To avoid going
to an empty cell above the bottom cell, try something like this:

Sub test()
Dim r1 As Range, r2 As Range
Set r1 = ActiveCell

Set r2 = Cells(65536, r1.Column).End(xlUp).Offset(1, 0)
MsgBox r2.Address
r2 = "hello"
'r2.select ' probably not necessary
End Sub

Regards
Peter


"Paul" wrote in message
om...
Hi,
I would appreciate some help with this macro as I am still grabbling with
these.
I have recorded the Macro below.

My problem is, pasting.
The line - Selection.End (xlDown)).Select
stops on the last filled in row, and a Paste will
overwrite that last cell.
So I recorded a down arrow move and the code selects that cell col and
Row,
Range ("A129").select, but next copy and paste needs to append data
onwards
from that last row.

How do I code it to jump down one row when I do the Selection.End
(xlDown)?

Thanks In Advance
Cheers Paul

Sub Copyfive()
'
' Copyfive Macro
''
Windows("Source.xls").Activate
Range("A9").Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Windows("Destination.xls").Activate
Range("A9").Select
Selection.End(xlDown).Select
Range("A129").Select
ActiveSheet.Paste
End Sub