Copy Paste Macro

meb

New Member
Joined
May 13, 2003
Messages
31
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
:rolleyes:
 

Some videos you may like

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.

LTunnicliffe

Well-known Member
Joined
May 8, 2003
Messages
767
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

Well-known Member
Joined
Aug 19, 2003
Messages
5,703
Platform
  1. Windows
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.

Code:
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

MrExcel MVP
Joined
Jul 21, 2002
Messages
73,092
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

New Member
Joined
May 13, 2003
Messages
31
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.
:oops:

Thanks to all!
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,720
Members
414,401
Latest member
grenona2020

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top