Select Workbook and Worksheet in Vlookup Macro

[tinydancer]

I have a macro that runs a vlookup. It's tailored not to overwrite cells that already have text in them and only carry over found values to blank cells. At the moment it simply goes looking for these values on another sheet in the same workbook called "Lookup_Sheet".

Sub Vlookup44()                                                                                                                 
Dim i As Integer                                                                                                                
Dim stVal As String                                                                                                             
Dim lastRow As Long                                                                                                            
On Error GoTo err_handler                                                                                                      
lastRow = Range("E" & Rows.Count).End(xlUp).Row                                                                                 
For i = 2 To lastRow                                                                                                           
    If IsEmpty(Range("Q" & i)) Then                                                                                            
        stVal = Application.WorksheetFunction.Vlookup(Range("E" & i).Value, Sheets("Lookup_Sheet").Range("E:T"), 13, False)     
        On Error Resume Next                                                                                                   
        Range("Q" & i) = stVal                                                                                                 
    End If                                                                                                                     
    
    If IsEmpty(Range("R" & i)) Then
        stVal = Application.WorksheetFunction.Vlookup(Range("E" & i).Value, Sheets("Lookup_Sheet").Range("E:T"), 14, False)
        On Error Resume Next
        Range("R" & i) = stVal
    End If
    
    If IsEmpty(Range("S" & i)) Then
        stVal = Application.WorksheetFunction.Vlookup(Range("E" & i).Value, Sheets("Lookup_Sheet").Range("E:T"), 15, False)
        On Error Resume Next
        Range("S" & i) = stVal
    End If
    
    If IsEmpty(Range("T" & i)) Then
        stVal = Application.WorksheetFunction.Vlookup(Range("E" & i).Value, Sheets("Lookup_Sheet").Range("E:T"), 16, False)
        On Error Resume Next
        Range("T" & i) = stVal
    End If
Next
Exit Sub
err_handler:
    MsgBox Err.Description
End Sub

What I want is to be able to run the code and let me select the file the vlookup pulls from. Essentially it would take me to My Computer and I would click on the excel workbook I want and then I would select the exact worksheet that I want to use for the vlookup. I would really appreciate any and all help I can get on this one.

 

[tinydancer]

@dmt32 I have no idea what yours is doing. It has me running a macro within a macro and when I go to select a sheet it simply does nothing when I click on it, not even an error.

 

[dmt32]

@dmt32 I have no idea what yours is doing. It has me running a macro within a macro and when I go to select a sheet it simply does nothing when I click on it, not even an error.

The test code calls the function to display the GetOpenFile dialog. When you open a workbook it should pass an instance of worksheet "LookUp_Sheet" from that workbook to your VlookUp44 code which as I stated, is largely unaltered from your published code save for the sheet qualifications.

I was not able to test solution but if code does not work would be helpful if you place copy of your workbooks with sample data in a dropbox & provide link.

Dave