CODEWITHSUNDEEP
excelvbacopy-pasteshapes
Mehdi
Mehdi

Reputation: 1531

How to paste a Shape after existing Shapes in Excel Sheet using VBA?

I want to copy a Shape and paste it a Sheet already containing one or more Shapes. I tried using the following simple code :

myShape.Select
Selection.Copy
ActiveWorkbook.Sheets(mySheet).Paste

But it pastes it above the existing Shapes in the Sheet ...

Is there a solution to detect the end of the existing shapes or to paste directly after ? Thx

Upvotes: 2

Views: 19190

Answers (1)

Siddharth Rout
Siddharth Rout

Reputation: 149295

Is this what you are trying?

Sub Sample()
    Dim myShape As Shape

    Set myShape = ActiveSheet.Shapes("Rectangle 1")

    myShape.Copy

    ActiveSheet.Paste

    With Selection
        .Top = myShape.Height + 10
        .Left = myShape.Left
    End With
End Sub

If there are more shapes then you will have to loop through all the shapes and then find the last shape and take that shape's .Top and .Height into consideration.

See this example

Option Explicit

Sub Sample()
    Dim myShape As Shape, shp As Shape
    Dim sHeight As Double, sTopp As Double

    For Each shp In ActiveSheet.Shapes
        If shp.Top > sTopp Then
            sTopp = shp.Top
            sHeight = shp.Height
        End If
    Next

    Set myShape = ActiveSheet.Shapes("Rectangle 1")

    myShape.Copy

    ActiveSheet.Paste

    With Selection
        .Top = sTopp + sHeight + 10
        .Left = myShape.Left
    End With
End Sub

Upvotes: 6

Related Questions