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

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,573
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
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
 

Some videos you may like

Excel Facts

Did you know Excel offers Filter by Selection?
Add the AutoFilter icon to the Quick Access Toolbar. Select a cell containing Apple, click AutoFilter, and you will get all rows with Apple

Domenic

MrExcel MVP
Joined
Mar 10, 2004
Messages
19,303
The following macro assumes that the sheet containing the rectangle to copy and the range to copy to is the active sheet...

Code:
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!
 
Last edited:

tonywatsonhelp

Well-known Member
Joined
Feb 24, 2014
Messages
2,573
Office Version
  1. 365
  2. 2019
  3. 2016
Platform
  1. Windows
Brilliant,
Thank you so much Domenic, this will save me soooo much time :)
Thanks
Tony
 

My Aswer Is This

Well-known Member
Joined
Jul 5, 2014
Messages
16,829
Office Version
  1. 2013
Platform
  1. Windows
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,001
Messages
5,526,196
Members
409,686
Latest member
Tori83

This Week's Hot Topics

Top