Move a Picture with Excel VBA

The following Excel VBA example will move a picture from one sheet to another with a little Excel VBA.  In doing so there are a few things to consider - firstly what is the name of the picture and where do we want to put the picture when we trap the name.  Finally what do we want to do with the picture our selection is replacing?  For this project I will use a data validation list to choose a country (flag) and Excel VBA will do the rest.  The following is a picture of the attached file which will outline the procedure.

All of the pictures (flags) have been given a name (China, Canada, Bahamas etc.) These are added to a validation list. To run simply select the picture to move from the drop down in blue and click on the move button. This procedure could also be adapted to work with a worksheet change event.

The following Excel VBA procedure is what sits behind the move button.

Option Explicit

Sub MovePicture() 'Excel VBA procedure to move a picture.
Dim sh As Worksheet
Dim Pic As Object

Set sh=Sheet2
Application.ScreenUpdating=False
'Remove any pictures on the destination sheet.
For Each Pic In Sheet1.Pictures
Pic.Delete
Next Pic
'Copy the picture as chosen by the dropdown. Paste in Sheet1
sh.Shapes([e13].Value).Copy
Sheet1.[d8].PasteSpecial
Application.ScreenUpdating=True
End Sub


There are two parts to the above procedure, firstly delete all the pictures from the destination sheet (Sheet1 is the destination sheet). Then copy the picture indicated in Cell E13 to D8 of Sheet1.

The attached example shows a working copy of this VBA technique.