Dynamically move linked pictures, to avoid overlaps / blank gaps between them

[jorbarrientos]

Hey everyone,

I was hoping someone could help with some ideas. I have an Excel report with multiple linked pictures, one after another in a vertical layout. Each of these linked pictures points to a named range, and each of these named ranges points to a specific pivot table. Some of these pivot tables row count (and therefore height) changes, depending on the filter value I select in a slicer that is connected to both pivot tables; however, in my final report, I need the linked pictures that reference these pivot tables to be exactly one below the other, with no overlaps, but no blank gaps between them. This has to update everytime I change my slicer value.

The problem right now is that everytime I select a new value in the slicer, if the linked picture at the top grows in height, it overlaps with the linked picture below; and viceversa, if the linked picture at the top shrinks in height, there is a huge white space between that picture and the linked picture below.

Is there a way to automate the behaviour I am looking for? Any help would be greatly appreciated!

 

[Yongle]

Is this what you want

From this

To this

If it is, amend this code to suit your needs

Sub LoopPics()
    Dim shp As Shape, cel As Range
    For Each shp In ActiveSheet.Shapes
        Set cel = shp.TopLeftCell
        If cel.Column = 2 Then
            With shp
                .Left = cel.Left
                .Top = cel.Top
                .Height = cel.Height
            End With
        End If
    Next
End Sub