run-time error 1004, microsoft office excel cannot paste the data

[madiedk]

Hey I have this code in excel that fails

Sub test()

Dim p As Object
Dim i As String
i = 1
Do While i < 10
Worksheets("sheet1").Select

Range("a1").Select


Selection.Copy


Set p = ActiveSheet.Pictures.Paste


i = i + 1
Loop

End Sub


it fails at line "Set p = ActiveSheet.Pictures.Paste" and says
run-time error 1004, microsoft office excel cannot paste the data

But not always, Sometimes i works other times is stops after 4 loops, 5 loops etc.

I use office 2007.

[Blade Hunter]

Hey I have this code in excel that fails

Sub test()

Dim p As Object
Dim i As String
i = 1
Do While i < 10
Worksheets("sheet1").Select

Range("a1").Select


Selection.Copy


Set p = ActiveSheet.Pictures.Paste


i = i + 1
Loop

End Sub


it fails at line "Set p = ActiveSheet.Pictures.Paste" and says
run-time error 1004, microsoft office excel cannot paste the data

But not always, Sometimes i works other times is stops after 4 loops, 5 loops etc.

I use office 2007.

Firstly i is a long, not a string.

Secondly I don't understand why you are copying a cell but pasting a picture. Are you trying to paste as a picture?

[madiedk]

Yeah i is a long in my code aswell now.

What I am doing is to make a cell to a picture. I guess that is the only way to do it i VBA?

The thing is that it works sometimes, so the code must be correct in some sense. I dont know if it is some kind of buffer og thread problem in Excel.

The code I am showing in this example is a simplified version of the original, but the problem still occurs

[Blade Hunter]

You want to copy as a picture as opposed to pasting as a picture. And what is the purpose of the loop? I assume you want to do cells A1 to A10??

This will do it:


Sub test()
Dim i As Long
Worksheets("sheet1").Select
For i = 1 To 10
Range("A" & i).Select
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
ActiveSheet.Paste
Next
End Sub


Edit: Woohoo, this post just promoted me to a VBAX Contributor :)

[madiedk]

Thanks a lot. I will try that

Can you show me how to get the picture object that is pasted

something like:
dim p as object

set p = ActiveSheet.Paste

but that dont work.

it is so i can use

p.top = 45
p.left = 70

and so on...

[Blade Hunter]

Sub test()
Dim i As Long
Dim p As Object
Worksheets("sheet1").Select
For i = 1 To 10
Range("A" & i).Select
Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
ActiveSheet.Paste
Set p = Selection
p.Top = 45
p.Left = 70
Next
End Sub


After you paste the picture it is selected so we can use that.

Obviously put some smarts in there so that not every one gets dumped on top of the other

[madiedk]

it still fails, found out that if I use:

Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
'MsgBox "test"
Set p = Worksheets("til pdf").Pictures.Paste

it only fails very rarely.

but thats alot for the effort

[madiedk]

I meant this:

Selection.CopyPicture Appearance:=xlScreen, Format:=xlPicture
Set p = Worksheets("til pdf").Pictures.Paste

[madiedk]

hmm.... dont know why i looks like this...with font etc.