VBA run time error 1004-Method PasteSpecial of Object_Worksheet failed

[Rodrigues]

Hi there
I'm experiencing a problem with a piece of VBA code.
When user click save&close the userform comes up with run rime error 1004, than click debug and highlights code attached,

 newWS.PasteSpecial Format:="Bitmap", Link:=False, DisplayAsIcon:=False

run (F5) again it will close and save the userform, but sometimes attaches to the email and save the correct userform as a PDF and sometimes the previous userform.
What I noticed is that when check file Sheet1 has not been deleted.
Can anyone help, please?
Thanks in advance
R

 

[Chihiro]

That's because when it errors out, code to delete sheet does not run.

To avoid that issue, you can paste to new workbook instead of new sheet.

Change "Set newWs..." line to "Application.DisplayAlerts = True" to something like below.

    Set newWb = Workbooks.Add
    newWb.Sheets(1).PasteSpecial Format:="Bitmap", Link:=False, DisplayAsIcon:=False
    With newWb.Sheets(1).PageSetup
        .Orientation = xlLandscape
        .PrintArea = "$A$1:$R$50"
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PaperSize = xlPaperA4
    End With
    pdfName = "C:\PDF" & "\" & Me.Name & " " & Format(Now, "yyyy-mmm-dd hmm") & "-" & Me.Label1.Caption & ".pdf"
    newWb.Sheets(1).ExportAsFixedFormat Type:=xlTypePDF, _
        Filename:=pdfName, Quality:=xlQualityStandard, _
        IncludeDocProperties:=False, IgnorePrintAreas:=False, _
        OpenAfterPublish:=False
    Application.DisplayAlerts = False
    newWb.Close False
    Application.DisplayAlerts = True

 

[Rodrigues]

Chihiro
Thanks again, you are a lifesaver.

Can I ask that, how can I or what setting do I need to change so the userform (pdf) prints on a full A4 page? At the moment prints off all the userform but not as a full A4 size.
Regards
Rodrigues

 

[Chihiro]

Play around with available ".PageSetup" properties.

You may want to set it to portrait orientation instead of landscape, as your userform dimension has changed from when I helped you originally. Also, play around with .Zoom as well to best fit form to the page.

There's quite a few that you can adjust, not all of them are needed (I used bare minimum needed on my example to do what's needed).
https://msdn.microsoft.com/en-us/library/office/dn254188.aspx

 

[Rodrigues]

Thank you very much.
Regards
R