Microsoft Excel - ActiveSheet.Paste generates run-time error '1004' -- Excel 2007

Hello.

I'm having a problem similar to one that was addressed in this forum back in Nov 2008 (in fact that specific problem is how I found the forum) wherein while recording a macro the copy/paste works, but when I stop recording and try to execute it the paste fails. Unlike that issue, this is on a single worksheet.

I read through that post and attempted the solutions, even going so far as adding another worksheet into which I would do the paste, but all was for naught.

The original code looks like this:

Sub Macro4()
'
    Range("A3:A5000").Select
    Selection.Copy
    Range("E3").Select
    Application.CutCopyMode = False
    ActiveSheet.Paste
'    --- more code that doesn't get executed due to the error above
End Sub

On the off chance that the problem was related to the size of the range, I decided to try the following:

Sub Macro5()
'
    Range("L3").Select
    Selection.Copy
    Range("E3").Select
    Application.CutCopyMode = False
    ActiveSheet.Paste
'    --- more code that doesn't get executed due to the error above
End Sub

But, alas, this generates the same error.

I then ran Office Update to download/install all the latest fixes, but I'm still experiencing the same problem.

While I'm trying to copy the results of formulae, I tried Macro5 with a simple character string also, still no luck.

I am relatively new to excel macros and VBA, so I think there must be something I'm missing.

Can anyone provide enlightenment?

Thanks in advance,

Terry

Use something like this..

Application.CutCopyMode = False
Sheets("3-04 Up").Range("A1001:D2000").Copy
Sheets("Run ").Range("a1001").Select
ActiveSheet.Paste

For more info, check here..

http://www.vbforums.com/archive/index.php/t-295812.html

Thanks, that did the trick.

For what it's worth, the code I ended up with is as follows:

    Application.CutCopyMode = False
    Sheets("Active Servers").Range("A3:A5000").Copy
    Sheets("Active Servers").Range("E3").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
       

Using just Selection.Paste caused the formulae to be copied. I'm not sure why I have to focus both the Copy and Select, but without both I get the 1004.

Thanks again,

Terry