VBA and shapes, man! It's all about the shapes!

[RockandGrohl]

Hi ladies and gents.

I have a sheet with various metrics on it (15 in total)

According to the variance from last month, these will either have a RedUp, RedDown, GreenUp, GreenDown or GreySame box with an inside arrow (grouped shapes)

Don't worry too much about that, I will have to do it line by line for each of the 15 metrics and work out is it a profit increase (green up) a cost increase (red up) etc, that sort of thing.




I have these five grouped shapes sat in the top of the worksheet, and when I need one, I want to duplicate it, and then move that named version into place. But first, I need to delete all shapes that aren't the original five.

Here's what I have so far:

Set s = ActiveSheet.Shapes("RedUp")

Set s = s.Duplicate


With s
    .Top = Range("B9").Top
    .Left = Range("C9").Left - 27.5
End With

The problem here is that when I duplicate the shape, it retains the original name of "RedUp" - it would be good to call it "RedUpCopy" and then at the start of the module I can simply purge any shapes that have "copy" in the name.

Thanks! Leslie, there's not a single shape in that painting!

 

[RockandGrohl]

Ah! This looks promising:

Set s = ActiveSheet.Shapes("RedUp")

With s.Duplicate
    .Name = s.Name & "Copy"
    .Top = Range("B9").Top
    .Left = Range("C9").Left - 27.5
End With