Pasting Objects to New Workbook

Question

I would like to copy some textboxes and charts that are on a sheet and paste them on a newly created workbook for a report. So far I've been trying to modify a piece of code I found here. This is what I have so far:

Sub SampleIndividualReport()
    Dim wbI As Workbook, wbO As Workbook
    Dim wsI As Worksheet, wsO As Worksheet


    Set wbI = ThisWorkbook
    Set wsI = Sheet7

    Set wbO = Workbooks.Add

    With wbO
        Set wsO = wbO.Sheets("Sheet1")
        ActiveWindow.DisplayHeadings = False
        Application.DisplayFormulaBar = False
        ActiveWindow.DisplayGridlines = False

        .SaveAs ThisWorkbook.Path & "\" & GetSelectedSlicerItems("Slicer_Teacher") & ".xlsx"

        wsI.Range("D39:BR97").Copy

        wsO.Range("D7").PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone _
        , SkipBlanks:=False, Transpose:=False

        wsO.Range("D7").PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False


        wsI.Shapes.Range(Array("Chart 29", "TextBox 30")).Select
        Selection.Copy

        wsO.Range("G31").Select
        wsO.PasteSpecial Format:="Picture (Enhanced Metafile)", Link:=False _
        , DisplayAsIcon:=False

        .Save

   End With
End Sub

That last PasteSpecial gives me: Run-time error '1004': Method 'PasteSpecial' of object '_Worksheet' failed.

Is `wsO` the `ActiveSheet`? Try putting `wsO.Activate` before the `.Select` call. — Mathieu Guindon, Sep 19 '18 at 21:22
@MathieuGuindon - I'd instead recommend that OP [avoid using `.Select`/`.Activate` altogether](https://stackoverflow.com/questions/10714251/how-to-avoid-using-select-in-excel-vba). Also a personal thought, those worksheet/workbook variable names could be a little better/more specific/explicit. — BruceWayne, Sep 19 '18 at 21:36
@MathieuGuindon Thanks for your comment. I tried this (pasting Range instead of selecting it) before, although I get "Compile error: Named argument not found" while Format:= is highlighted — user3487128, Sep 19 '18 at 21:36
`Format` is not an option with `PasteSpecial`. Nothing after `PasteSpecial` is valid. Try replacing all 3 arguments with `xlPasteAll` — Darrell H, Sep 19 '18 at 21:52
@DarrellH When doing this, it pastes the previosly pasted data (Range("D39:BR97)) but not the textbox and the chart... — user3487128, Sep 19 '18 at 21:58
Delete the line that has `wsO.Range("G31").Select` and make the next line `wsO.Range("G31").PasteSpecial xlPasteAll`. You did selection.copy, then selected again. — Darrell H, Sep 19 '18 at 22:02
@DarrellH that I did. As I mentioned before the previously pasted data is pasted again, but not the chart nor the textbox. Thanks! — user3487128, Sep 19 '18 at 22:06
@DarrellH OP is using [`Worksheet.PasteSpecial`](https://learn.microsoft.com/en-us/office/vba/api/excel.worksheet.pastespecial), which does have a `Format` argument — BigBen, Sep 20 '18 at 13:09

score 0 · Answer 1 · answered Sep 19 '18 at 22:58

0

Okay, here is a working solution starting from where your problem starts

wsI.Shapes.Range(Array("Chart 29", "TextBox 30")).Select
Selection.CopyPicture xlScreen, xlPicture

wsO.Paste
Selection.Name = "NewShape"
With wsO.Shapes("NewShape")
    .Top = wsO.Range("G31").Top
    .Left = wsO.Range("G31").Left
End With

answered Sep 19 '18 at 22:58

Darrell H

1,876
1
9
14

Thanks for your answer. Although no error is reported, nothing gets pasted in the new workbook! – user3487128 Sep 20 '18 at 17:58
That's strange. I tested it and it worked. I've used in previous code. – Darrell H Sep 20 '18 at 19:44
Now, if I paste in a sheet in the current workbook and save that sheet as a new workbook all works fine... – user3487128 Sep 20 '18 at 19:46

Pasting Objects to New Workbook

1 Answers1