Copy Paste Macro

[meb]

I am having a problem with the following macro:

Sub CopyPasteSpecial()
Application.ScreenUpdating = False
Range("A3:A61").Select '(The range I’m copying)
Selection.Copy
Workbooks.Open Filename:="C:\My Documents\Special\Handicaps.xls" '(The file I’m pasting to.)
Sheets("Scores").Select '(The sheet I’m pasting to)
[B1].Select '(The column I want to begin my paste in…(The dates are in Column A))
Selection.End(xlDown).Select '(Goes to my last entry in this column)
ActiveCell.Offset(1, 0).Select '(Drops down to the next row, in Col.B)
Selection.PasteSpecial xlValues
Application.ScreenUpdating = True
End Sub

The macro stops at: ActiveCell.Offset(1, 0).select

I am trying to copy a range (a3:a61) from one workbook and
paste it into another workbook starting at range b1 of the second
workbook.
Any help would be greatly appreciated.
Thank you

 

[LTunnicliffe]

The problem is probably that there is no data in you destination workbook so the .End(xlDown) statement drops the active cell to the bottom then you try to move down one more row, which is impossible.

 

[onlyadrafter]

Hello,

I'm guessing the problem is that there is no data in B1 so the cursor is going to the last row of the ss.

Try the amended code, and see if it is OK.

Sub CopyPasteSpecial()
Application.ScreenUpdating = False
Range("A3:A61").Select '(The range I’m copying)
Selection.Copy
Workbooks.Open FileName:="C:\My Documents\Special\Handicaps.xls" '(The file I’m pasting to.)
Sheets("Scores").Select '(The sheet I’m pasting to)
Range("B65536").End(xlUp).Offset(1, 0).Select
Selection.PasteSpecial xlValues
Application.ScreenUpdating = True
End Sub

 

[Andrew Poulsom]

It will fail if the ActiveCell is in row 65536 (ie there are no entries below B1). Why are you going End Down from cell B1?

 

[meb]

Onlyadrafter,
Thank you very much. Your macro works beautifully.
I appreciate the time and the reply.

To all others:
I don't know why I was going xlDown. Brain dead only logical answer.


Thanks to all!