Copy a shape into every cell in range and get text into it?

[tonywatsonhelp]

Hi everyone

For years I've created shapes and put the text in as cell value.
I need to copy a shape to a huge number of cells but when i copy paste it keeps the cell reference of the last shape "=F123" for example
so is there an easy way to fix this?
if not would you help me with a macro to do the following
Copy Shape "Rectangle 1" and paste it into every cell in range G115:AD127 with the text being the cell ref but down 30 rows

Every cell is set to fit the shapes,

please help if you can or I'll be hear all night lol

Thanks

Tony

 

[Domenic]

The following macro assumes that the sheet containing the rectangle to copy and the range to copy to is the active sheet...

Sub CopyRectangleToEachCellInRange()

    Dim shp As Shape
    Dim rng As Range
    Dim cel As Range
    
    Application.ScreenUpdating = False
    
    Set shp = ActiveSheet.Shapes("Rectangle 1")
    Set rng = Range("G115:AD127")
    
    shp.Copy
    For Each cel In rng
        With ActiveSheet
            .Paste
            With .Shapes(.Shapes.Count)
                .Left = cel.Left
                .Top = cel.Top
                .DrawingObject.Formula = "=" & cel.Offset(30).Address
            End With
        End With
    Next cel
    
    Application.ScreenUpdating = True
    
End Sub

Hope this hleps!

 

[tonywatsonhelp]

Brilliant,
Thank you so much Domenic, this will save me soooo much time
Thanks
Tony

 

[My Aswer Is This]

I'm always curious.
I never understand why someone would need

312 Shapes on a sheet.

I use excel all the time and I get by with hardly any shapes. Maybe 3 or 4

Most of my scripts are run using a short cut key or I choose option Buttons on a Userform.

You can also double click a cell to run a script or right click a cell to run a script.



Would you care to say what all these Shapes will be doing.
And then you will somehow have to add a script to these shapes if you plan to use them to run scripts.

Just curious in case you would like to explain what you plan to do with all these shapes.