CODEWITHSUNDEEP
excelvbashapes
Antoni Gual Via
Antoni Gual Via

Reputation: 763

How can I duplicate a shape in a different Sheet?

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
....

Upvotes: 0

Views: 1451

Answers (2)

FaneDuru
FaneDuru

Reputation: 42256

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...

Upvotes: 0

Christofer Weber
Christofer Weber

Reputation: 1474

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)

Upvotes: 1

Related Questions