I have approximately 150 seperate excel files. What I am attempting to do is have the user enter a value in Worksheet A, then I need it to take that value and find the corresponding file and display it in worksheet A. Is this possible?
Yes, all files will have the same file path. The file names correspond to what is being entered in B2 of worksheet A. Each file has different data that I need to display within worksheet A. It would would be awesome if it could display it just by making the entry in B2.
OK, try this in the sheet module for the sheet of choice. (In the workbook you want to
make the B2 entry in and have the copied data pasted to.)
Right click the sheet tab (of the sheet you'll make the B2 entry in) and choose View code.
Paste this into the sheet module. (White area on the right.)
Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Count > 1 Then Exit Sub
If Intersect(Target, Range("B2")) Is Nothing Then Exit Sub
If Target = "" Then Exit Sub
Dim strDir$, fName$
strDir = ThisWorkbook.Path & "\"
fName = Target.Value & ".xls"
Application.ScreenUpdating = False
On Error Resume Next
Workbooks.Open strDir & fName
If Err = 0 Then
MsgBox ("The file '" & fName & "' does not exist in the folder being searched."), , fName & " not found"
Application.ScreenUpdating = True
The below code that you gave me works perfectly. However, now they would like me to also display a picture along with the data. I have the pictures in a .jpg in the same directory with the same name of the value that is entered in B2. The only catch is that I need to to resize the picture to 3" wide by 4" high. Can this be done with a simple modification to this code?