Paste method error 1004

[rudytor]

The following fails with "Paste (or PasteSpecial) method of Worksheet class failed". This is code on a form button.

ActiveSheet.Range(Cells(2, 1), Cells(2000, 1)).NumberFormat = "@"
Sheets("LoanCopy").Activate
Range("A2").Select
'ActiveSheet.PasteSpecial
ActiveSheet.Paste

I am copying a little set of either numbers or text-fornatted numbers from another area of the sheet. Manually it works. If I create and run a macro to paste from the clipboard or copy/paste it also works.

0999350325
0999350325
0999462856
1075001086
1099001160
1100132637

If I copy the numbers from a SQL Server query window or copy text from Notepad, for example, it works like a champ. I have googled quite a bit but nothing seems to work. TIA.

SOLVED (sorta) If I eliminate the NumberFormat line all is well, but why?

[lucky027]

I tried your code and have an error too
Sorry but I didn't see the copy code in this code ?
Could you try with this code

Code: Select all

ActiveSheet.Range(Cells(2, 1), Cells(2000, 1)).NumberFormat = "@"
ActiveSheet.Range(Cells(2, 1), Cells(2000, 1)).Copy
Sheets("LoanCopy").Activate
Range("A2").Select
'ActiveSheet.PasteSpecial
ActiveSheet.Paste

that's working for me
Hope that helps

[venkat1926]

rudytor
I am confused
what cell or range is copied. no mention of "copy" anywhere in the code.

[Sisyphus]

Hi,
The first line of code in the original post sets the number format for the first 2000 cells in column A to "Text". That raises the question of what you want to paste there, and if you wanted to paste numbers you would expect the system to be able to convert them to text format. If the system decides that it can't you will get an error message.

As Lucky027 has pointed out, there is no command to copy. Instead, there are two commands to paste, first Paste Special, then a normal Paste. Neither of these commands has a destination cell. That, by itself, is worth an error message.

If Excel should manage to find a default to paste to, the same value will be pasted twice to the same cell. You will never see the result of the Paste Special operation because it is immediately over-written by the Paste.

So, to cure your problems do two things:
1. Make sure that the format of the target cell isn't different from that of the data you want to paste there. If in doubt, format as "General". "General" is a number format understood by Excel, like "@".
2. Specify a destination cell. The target is always the left top corner of any range you paste. If you specify a larger range it must be of the exact same size as the range you are pasting. So, instead of ActiveSheet.Paste, use

Code: Select all

ActiveSheet.Cells(1, 1).Paste


That should put and end to the error 1004 messages.

[rudytor]

To clarify, the .PasteSpecial line was REM'd out so only a .Paste was being done. The .Copy is actually being done manually from another xlsx. I had to format the target range as Text so that leading 0's would not be lost in the .Paste. However, it seems that if I manually format the target range to Text and save the xlsx I don't need the .NumberFormat statement and it works fine. It just seems that a simple operation like what I was doing should not result in thew 1004 error - which I saw a lot of in googles.

Bottom line it works I really appreciate the suggestions and comments.

[lucky027]

what you want to do is not possible !!!
the manually copy is disabled by the numberformat code
you should put the copy code after the format code like I suggest you in the correction

It's very easy to understand that try in step by step with your code and look at the selection highlight

One possibility is to record your selection before formating and use copy mode for your selection after.
Could you try this code ?

Code: Select all

Sub rudytor_code()
Dim MyRange As Range
Set MyRange = Selection
ActiveSheet.Range(Cells(2, 1), Cells(2000, 1)).NumberFormat = "@"
MyRange.Copy
Sheets("LoanCopy").Activate
Range("A2").Select
'ActiveSheet.PasteSpecial
ActiveSheet.Paste
End Sub

hth