Run-time error '1004' using PasteSpecial

[gersemale]

Hi folks,

Having trouble with some simple code. I am pasting some data from the clipboard.

I receive the error msg: Run - time error '1004' PasteSpecial Method of range class failed
[VBA]
Worksheets("Paste Sheet").Range("AX2:AZ50").Clear
Worksheets("Paste Sheet").Cells(2, 50).PasteSpecial (xlAll)
[/VBA]

xlAll has the value -4104

I am definately coping the data before I paste it.

I have tried other options including selecting and activating the sheet first.

Any one have any ideas plz?

Thanks a million

ger

[malik641]

I think it might be because you clear cells before using the PasteSpecial method. If you record a marco and copy a set of cells then delete others, you lose what you copied even if what you deleted had no affect on those copied cells. So there is no range to paste because it was discarded.

Hope (I'm right and) this helps

[gersemale]

That works thanks a million.... but why does the same error appear when i use the following code:

[VBA]
Worksheets("Paste Sheet").Range("BC2").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
[/VBA]

Is it because im selecting the cell BC2 first?

The reason I am using a paste special text only here as on one other PC i have tested my code on the data I am copying (from a CSV file) is sometimes put in as a picture!

Any help again much appreciated!

[malik641]

[VBA]
Worksheets("Paste Sheet").Range("BC2").Select
ActiveSheet.PasteSpecial Format:="Text", Link:=False, DisplayAsIcon:= _
False
[/VBA]

Is it because im selecting the cell BC2 first?

The reason I am using a paste special text only here as on one other PC i have tested my code on the data I am copying (from a CSV file) is sometimes put in as a picture!

Any help again much appreciated!

If the cell is to be formatted as text and whatever you are copying is not already formatted as text, then I think that you cannot format the cell as you are pasting, it has to be formatted before doing so.

[VBA]
Worksheets("Paste Sheet").Range("BC2").Select
Selection.NumberFormat = "@" 'Formats cell to text
ActiveSheet.PasteSpecial Link:=False, DisplayAsIcon:= False

'Now if the cell that you copied is already text formatted, then:

Worksheets("Paste Sheet").Range("BC2").Select
ActiveSheet.PasteSpecial Paste:=xlFormats, Link:=False, DisplayAsIcon:= False
[/VBA] This should do it
HTH

[gersemale]

Hey, thanks for your reply however I am still experiencing the same problem. Same error msg appearing.

Further note that PasteSpecial(xlAll) works on my PC but not on another with the same version of excel!

Any further ideas?

[Shazam]

Hi gersemale,

I tried all the codes that was provided on this thread and I do get the same error BUT then I named my worksheet tab Paste Sheet and all the codes works fine now. Maybe just name your worksheet Paste special.

[gersemale]

Thanks Shazam!