VBA - Paste image from my clipboard below the last image

[christiancdv]

Hello, i need help in the creation of the macro. Ill try to explain the function i want it to have:

- With the screenshot already on my clipboard, I need all the different screenshot, that are going below the blue rectangle, to mantain the same width (30,4cm) and column (B:B) of this rectangle).
Below I show an example of what I need. Therefore, every time I paste a new screenshot the macro should search the last image and put the new one beneath the last one.
- Also, it would be perfect if between each screenshot it would mantain a gap.

Best regards,
Christian.

 

[christiancdv]

@DanteAmor can you help me with this task?

 

[christiancdv]

Something similar to what I want to get..

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("Label")

    myShape.Copy

    ActiveSheet.Paste

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

 

[christiancdv]

Sub LineUpAllShapes()
    Dim ws As Worksheet
    Dim shp As Shape, shpHeader As Shape
    Dim colOtherShapes As New Collection
    Dim prvShape As Shape
    Set ws = ActiveSheet
    
    For Each shp In ws.Shapes
        If shp.TopLeftCell.Column = 2 And shp.TopLeftCell.Row = 2 Then
            Set shpHeader = shp
        Else
            colOtherShapes.Add shp
        End If
    Next shp
    
    If Not shpHeader Is Nothing Then
        Set prvShape = shpHeader
        
        For Each shp In colOtherShapes
            shp.Width = prvShape.Width
            shp.Top = prvShape.Top + prvShape.Height + 5
            shp.Left = prvShape.Left
            
            'add border to image
            shp.DrawingObject.Border.LineStyle = 1
            shp.DrawingObject.Border.Color = vbBlack
            shp.DrawingObject.Border.Weight = 2
            
            Set prvShape = shp
        Next shp
    End If
End Sub