Search all sheets in workbook for cell value and insert image

[Pikachu90]

I'm having trouble with my macro and I feel like I'm not seeing things clearly anymore, any help is helpful, especially with the idea of looping

1. Prompt user for folder
(I have this done and I stored it as MyDir as a string)

2. Folder contains image files with names that are found in a cell in a particular sheet
(So imageABC.emf has a sheet Apples which contains a cell that =imageABC.emf)

3. After picking up the file name imageABC.emf, the macro looks through the active workbook for the cell containing that value, and inserts the file "in" the cell.

4. Then it loops back and repeats the process for the rest of the folder contents. If the file name is not found in the workbook, it's skipped (or msgbox alert) and continues with the rest


Thanks in advance. I will post what I have as soon as I charge my laptop

 

[Pikachu90]

Sub Macro5()

Dim MyDir As String

With Application.FileDialog(msoFileDialogFolderPicker)
.AllowMultiSelect = False
If .Show <> -1 Then MsgBox "No Folder Selected! Exit Sub.": Exit Sub
MyDir = .SelectedItems(1)

End With

'------------------------------------------------------------------------------

Dim FilePath As String
Dim FileName As String
Dim File As String

Dim WrkSheet As Worksheet
Dim FoundName As Variant
Dim CellAddress As Variant



FilePath = MyDir & "\"
FileName = Dir(FilePath, vbNormal)

For Each WrkSheet In ActiveWorkbook.Worksheets

With WrkSheet
Set FoundName = .UsedRange.Find(what:=FileName)
If Not FoundName Is Nothing Then 'check to see if CellAddress is something Then
File = FilePath + FoundName

MsgBox FoundName & " FoundName"


CellAddress = FoundName.Address

activesheet.Pictures.Insert (File)

Else

MsgBox "Helloooo There"

End If
'FileName = Dir
End With
Next WrkSheet




End Sub

'CURRENTLY SEEMS TO BE ABLE TO LOOK THROUGH ALL SHEETS FOR THE FIRST FILE NAME IT GETS FROM THE FOLDER AND PASTES EQUAL NUMBER
'OF IMAGES INTO ACTIVE CELL AND NOT CELL THAT THE "FIND" FINDS but ONLY WHEN THE FIRST FILE SEARCHED FOR EXISTS IN THE ACTIVE SHEET?