Looking for a less time consuming way to paste in pictures to excel

[nlarski]

So I created a picture lookup where depending on a cell value, pictures associated with that value will populate. I have one sheet that is the lookup and another that is the driver which contains every picture. This file is a beast and changing anything is pretty slow.

I am now in the process of updating all of the pictures in the driver sheet and deleting the old. To do this I am copy and pasting the new and then resizing it which takes about 30 seconds per image. The problem is that I have almost 300 images so this has turned into a pretty time consuming and daunting task.

Is there any way that I can simplify or speed up this process? Something like pasting multiple pictures in at a set size?

Thanks!

 

[Haluk]

Try the below given code and modify it to suit your needs, if necessary. The code; inserts all the *.jpg files found in a folder and imports them on the sheet, starting from cell A1 to downwards.

Sub ImportPicturesFromFolder()
    PicW = 50
    PicH = 50
    intResult = Application.FileDialog(msoFileDialogFolderPicker).Show
    If intResult <> 0 Then
        strFolder = Application.FileDialog(msoFileDialogFolderPicker).SelectedItems(1)
        Set FSO = CreateObject("Scripting.FileSystemObject")
        Set MyFolder = FSO.GetFolder(strFolder)
            For Each MyFile In MyFolder.Files
                i = i + 1
                If Right(MyFile.Name, 3) = "jpg" Then
                Range("A" & i).Select
                Rows(i).RowHeight = PicH
                PicTop = Range("A" & i).Top
                PicLeft = Range("A" & i).Left
                Set MyPic = ActiveSheet.Shapes.AddPicture(MyFile, True, True, PicLeft, PicTop, PicW, PicH)
                End If
            Next
    End If
    Columns("A:A").ColumnWidth = 15
End Sub