How can I duplicate a shape in a different Sheet?

Question

I have a sprite sheet with country flags and code that puts the shape of a flag in each cell of a selection with two letter country codes. This works well if i .Addpicture from an external .png file for each cell. When i try to put the .png in an auxiliary sheet and .Duplicate flags from it, the flags are created in the sheet where the spritesheet is, no matter what I try. Stripped example below

How Can I .Duplicate a shape in a different Sheet?

Dim sh,shf as shape
dim is as range
Dim ws As Worksheet: Set ws = ActiveSheet
Set shf = Worksheets("flags").Shapes("flags")
ws.Activate
...
For each i in Selection.Cells
           'get country code and calculate offsets 
           Set sh = shf.Duplicate   'this puts the shape in the flags sheet
....

Hi, have you checked [https://learn.microsoft.com/en-us/office/vba/api/excel.shape.duplicate] ? The method does not offer the option to duplicate in a different location. I think what you need to do is use this method [https://learn.microsoft.com/en-us/office/vba/api/excel.shape.copypicture] and then paste the content of the clipboard on the sheet where you wish to. Hope his helps. — EarlyBird2, Nov 10 '20 at 09:03
I had read the manual page about .Duplicate, unfortunately its too terse and forgets to state the copy goes always to the sheet the original shape is in. Thanks! — Antoni Gual Via, Nov 10 '20 at 09:29
BTW. I published here https://stackoverflow.com/questions/47920622/is-there-a-way-to-choose-the-correct-country-flag-depending-on-country-name-in-v the country flag macro that uses an external spritesheet file. — Antoni Gual Via, Nov 10 '20 at 10:25

Christofer Weber · Accepted Answer · 2020-11-10T09:14:41.473

1

As according to other answers found, you cannot just move a shape from one sheet to another, and .Duplicate will just duplicate in the same place.

Easiest step might be to just copy and paste the shape to the new worksheet. Giving it a new name in the process.

Dim s1 As Worksheet, s2 As Worksheet
Dim shp2 As Shape

Set s1 = Sheets("Sheet1")
Set s2 = Sheets("flags")

s2.Shapes(yourflag).copy
s1.Paste
set shp2 = s1.Shapes(s1.shapes.count)

edited Nov 10 '20 at 09:14

answered Nov 10 '20 at 09:06

Christofer Weber

1,464
1
9
18

Thanks for your help! The answer you link to does'n mention .Duplicate at all.... – Antoni Gual Via Nov 10 '20 at 09:24
That's true. They mention that there's "there is no location property or move method", so I added that .Duplicate will just make another shape in the same place. It was intended more as some added info to back up my claims. – Christofer Weber Nov 10 '20 at 09:32
If you really want to move your shape, just use `.cut` instead of `.copy`. – EarlyBird2 Nov 10 '20 at 09:40

score 0 · Answer 2 · answered Nov 10 '20 at 09:18

As it has been stated, the Duplicate method does not offer the possibility to do that in a different sheet. But you can cut and paste the duplicated shape in the next way (in case you like duplicate and not using the clasic Copy - Paste solution):

Sub testShapeDuplicate()
 Dim sh As Shape, shf As Shape, dLeft As Double, dTop As Double
  Set shf = = Worksheets("flags").Shapes("flags")
  Set sh = shf.Duplicate 
  sh.Name = "Dupl": dLeft = shf.left: dTop = shf.top
  sh.Cut
  With ActiveSheet.Next 'it will be moved in the next sheet. Use here what sheet you need, please
    .Paste
    With .Shapes(.Shapes.count)
        .left = dLeft: .top = dTop 'put the shape in the same position
    End With
  End With
End Sub

Besides that, when you declare Dim sh,shf as shape, only the shf object is declared As Shape. sh is declared As Variant...

How can I duplicate a shape in a different Sheet?

2 Answers2