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.
Have a great day!
Sisyphus
I do this for "honour and country" - much less of the latter, actually.
If I helped you, award points, plenty of them.
If I bored you, deduct points for being too long-winded. (I know, :lol)