CODEWITHSUNDEEP
excelvba
Andreas
Andreas

Reputation: 23968

Copy paste shapes from cells

I have made a code that creates labels and barcodes for printing.
Due to other reasons (for simplicity) I have placed some labels on a separate sheet that I then need to transfer to the real sheet.
I found some mention about the CopyObjectsWithCells but it's not working for me.

Application.CopyObjectsWithCells = True
Sheets("Robot").Range("A1:L" & Lastrow).Copy
Sheets("Etikett").Range("A" & intRad).PasteSpecial Paste:=xlPasteAll

I get the same result Range().Select then Selection.Copy. 

Sheets("Robot").Shapes.SelectAll
Selection.Copy

Works. But it makes the pasted image one large image, not x small images/shapes. and gives a white background overlaying the other text on the sheet.
If I select the range in Excel and press Ctrl + C / V it copies the images as I want.
But with VBA it just won't work.

Example image
enter image description here

Upvotes: 0

Views: 12512

Answers (2)

Vityata
Vityata

Reputation: 43595

Whenever you have a problem, which is can be described with:

I have managed to do it manually in Excel, but I cannot find the correct VBA code.

a good solution is to use the macro-recorder option in VBA and see the code it generates, while you do the manual work in Excel. In your case, this is the code it makes, when it simply copies two shapes to another worksheet:

Sub Makro2()
    ActiveSheet.Shapes.Range(Array("Rectangle 1")).Select
    ActiveSheet.Shapes.Range(Array("Rectangle 1", "Rectangle 2")).Select
    Sheets("Tabelle2").Select
    Range("B4").Select
    ActiveSheet.Paste
    Range("M25").Select
End Sub

This code is really bad, but it works and it may give you good insights to work further.

Upvotes: 4

Foxfire And Burns And Burns
Foxfire And Burns And Burns

Reputation: 11998

After selecting and copying the range that contains the shapes, select the destination cell and then use ActiveSheet.Paste.

The option you are using PasteSpecial Paste:=xlPasteAll won't paste Shapes.

Upvotes: 1

Related Questions