I'm assuming, since you're turning off screen updating, that you're
not going for a cool visual effect. Therefore, I'd recommend
something like this instead. Let's say I'm copying cell A1 of Sheet2
to active sheet; the following will paste it into first available row
of column 1 on the active sheet:

With ActiveSheet
Sheet2.Cells(1, 1).Copy _
.Cells(Application.WorksheetFunction.CountA(.Range ("$A:$A")) + 1,
1)
End With

On Nov 28, 10:54 am, Steve wrote:
I have just installed Excel 2007 and wish to copy values from one worksheet
(data downloaded from online banking in csv format) to the first blank row in
another. I am manually copying a range of cells in the first worksheet and
then, in the second worksheet, running a macro which includes the code:

Application.ScreenUpdating = False
rownum = 4
Cells(rownum, 1).Select
While Selection.Value < ""
rownum = rownum + 1
Cells(rownum, 1).Select
Wend
ActiveSheet.Paste
. . . . and then various formatting functions

When I try to run the macro from a UserForm or by selecting it in the list
of macros dialog box, it fails with the message

"Run Time Error 1004 - Paste method of Worksheet class failed"

The macro runs OK from a keyboard shortcut.

I get the same problem if I test it by using Record Macro to create a paste
action and then run the recorded macro.

(Running Vista Home Premium)

Can anyone help please?