VBA insert an image multiple times without copy,paste

Question

Little bit of an odd question here.

On one of my excel sheets, I generate a pdf document.

The code runs by adding in segments of tables as required and manually setting a page break as needed so that a table isnt spread of a single page. (most tables are 5-10 rows)

at the bottom of each page, there is enough rows left over to insert an image. the image is just a few squares and a text box grouped together to provide a disclaimer and a signature box.

currently I have been using the

Sheets(...).shapes(...).copy
sheets(...).cells(...).pastespecial paste:=xlpasteall

with a few

doevents

to try make sure it runs correctly.

I keep running into the issues of 800401d0 method copy' of object 'shape' failed and error 1004 pastespecial method of range class failed.

I tthought it may have been becuase i did not have the doevents code but that has not remedied the situation.

I believe having the grouped shape saved as a single image embedded in the workbook may be easier so that I may use insert rather than copy.

the problem is I do not know how to go about this.

I could save the shapes as an image outside the worksheet, but when i do this, the quality drops and the text becomes blurry.

Any suggestions would be appreciated.

Thank you.

Fr the paste part maybe look here: stackoverflow.com/questions/60063795/… — Tim Williams
– Tim Williams, Commented Apr 20, 2021 at 3:44
Hi Tim. I saw the code you posted and am confused as to where i put it into my code. I am not to familiar with dealing with errors. so i paste your sub in to my sheet and then have in my sub "onerror go to pastepicretry"? — Matthew Wilcox
– Matthew Wilcox, Commented Apr 20, 2021 at 5:06

Tim Williams · Accepted Answer · 2021-04-20 06:03:37Z

2

I've had random odd problems pasting images in Excel, typically when running in a loop, and retrying the paste often succeeds, so you can try something like this, where the paste is retried until it succeeds (or a fixed number of tries still fail...)

Sub CopySomeThings()
    '...
    '...
    Thisworkbook.Sheets("source").Shapes("CopyThis").Copy
    PastePicRetry Thisworkbook.Sheets("destination").Range("A50")
    'etc etc
End Sub



'paste problem fix: keep trying until it works or for 20 tries
Sub PastePicRetry(rng As Range)
    Dim i As Long
    Do While i < 20
        On Error Resume Next
        rng.PasteSpecial
        If Err.Number <> 0 Then
            Debug.Print "Paste failed", i
            DoEvents
            i = i + 1
        Else
            Exit Do
        End If
        On Error GoTo 0
        i = i + 1
    Loop
End Sub

answered Apr 20, 2021 at 6:03

Tim Williams

169k8 gold badges104 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Matthew Wilcox Over a year ago

Hi Tim, Thanks for the response. I cannot seem to get it to work. I believe it is because my range refernce isnt an exact cell but instead it is calculated. so my cell in this example is cells(x-10,2). is there a way to convert this into a range suitable reference? thanks.

Tim Williams Over a year ago

What does it do instead of work? Cells() is equivalent to Range() as long as x is valid

Matthew Wilcox Over a year ago

Hi Tim, I found the issue and fixed it. Your solution appears to be working. thank you so much.

Collectives™ on Stack Overflow

VBA insert an image multiple times without copy,paste

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related