How does Excel name the images that are copied from a specific range?

[warviksam]

Hi,

I am trying to copy a data from a range and paste it as "linked image" in another range. Excel seems to define a new name for the image as it gets pasted each time. How do I define a name for this image?

What I want to do is to resize the pasted image to fit in a defined range. Please help me with the code to resize the image also. I have pasted what I have written so far.

Sub copy_image()

Set wsb = Worksheets("x")
Set wso = Worksheets("y")

wsb.Activate
Range("C1:H18").Select
Selection.Copy
wso.Select
Range("AG64:AY81").Select
wso.Pictures.Paste(Link:=True).Select
ActiveSheet.Shapes.Range(Array("Picture 104")).Select '--> Picture 104 is what was defined by excel for the last image it pasted. This is hard coded and cannot be used for anything else.

 

[DanteAmor]

Hi and welcome to MrExcel

Try this:

Sub copy_image()
  Dim img As Picture, sh As Worksheet
  Set sh = Sheets("y")
  
  'Delete previous image to add the new one and not have many images on it
  On Error Resume Next
  sh.Shapes("Picture y").Delete
  On Error GoTo 0
  
  'Copy and paste image
  Sheets("x").Range("C1:H18").Copy
  Set img = sh.Pictures.Paste(Link:=True)
  img.Name = "Picture y"
  
  'resize the image
  With sh.Range("AG64:AY81")
    img.Top = .Top
    img.Left = .Left
    img.Height = .Height
    img.Width = .Width
  End With
End Sub