Rename Picture to Value in a Cell & Copy to Worksheet

[nirvehex]

Hi,

I'm trying to figure out how to write some VBA that will (when executed) name a picture to the text value in cell B17 on the "Scope of Work" tab. The picture will be pasted on the "Scope of Work" tab and is the only image there.

Once the code is run I would like it to, like I said name the picture to the value in Cell B17, and then copy the picture to cell A2 (top left corner of it anyway) on the "Summary" tab.

Any code that can do this?

Thanks!

[Kenneth Hobs]

Post an example file with the image in it so we can more easily help.

[jolivanes]

Like Kenneth said, an example helps a lot but in the meantime, do you mean something like this?


Sub Add_And_Copy_Picture()
Dim impPic As Picture
Dim strPath As String, strFileName As String
Application.ScreenUpdating = False
strPath = "E:\E-Mail Downloads\" ' change folder to suit
strFileName = Dir(strPath & "Gedichtje By Guido Gazwelle.jpg") ' change file name and type to suit
With Sheets("Scope of Work")
Set impPic = Sheets("Scope of Work").Pictures.Insert(strPath & strFileName)
With impPic
.Name = Range("B17").Value
'the following lines can be changed or deleted as required
.Top = 0
.Width = 150
.Height = 150
.Left = 0
End With
End With
ThisWorkbook.Sheets("Scope of Work").Shapes(Range("B17").Value).Copy
Sheets("Summary").Range("A2").PasteSpecial xlPasteAll
Application.ScreenUpdating = True
End Sub

[nirvehex]

I'm not sure how to attach a sample file as I don't have anywhere to upload it to.

However, I follow your logic in the VBA above. That being said, this picture gets pasted into the Scope of Work tab and is the only image on that tab, so I'm not sure why I need the strPath. I don't want to change the actual file name, just the name of the image on the excel spread sheet.

I just want to find whatever image is pasted on this tab, change the name of it in the Excel document from 'Picture1' or whatever it gets imported as when I do "insert Picture" to the value of the string of text in cell B17 on the scope of work tab. Then I want to copy that image to the summary tab and paste it so the top left corner of it starts in cell A2 on the summary tab.

Hope this helps explain a little better. Thanks for the help thus far!

[Kenneth Hobs]

Click the Go Advanced button when you Reply. Then click the paperclip icon to attach your file. Then Add Files button, then Select Files, and so on. Most forums use this same method.