vba - Taking a value from other workbook but VLookup is not working

Question

The VLookup seems to be not working can`t workout why?? The other workbook opens up but does not produce the value?? When I had the code in a module it worked.

Private Sub ListBox4_Change()

    
    If Me.ListBox4 = "Fill Details" Then
    
       Application.ScreenUpdating = False
    
        Dim SrcOpen As Workbook
        Dim Des As Workbook
        Dim JCM As Worksheet
        Dim TGSR As Worksheet
        Dim FilePath As String
        Dim Filename As String
        Dim DesDataRange As Range
        Dim SrcDataRange As Range
        Dim LastRow As Long
    
        
        FilePath = "\TGS-SRV01ShareShopFloorPRODUCTIONJOB BOOK"
        Filename = "JOB RECORD SHEET.xlsm"
    
       
      
        Set SrcOpen = Workbooks.Open(FilePath & Filename)
        Set TGSR = SrcOpen.Worksheets("TGS JOB RECORD")
        LastRow = TGSR.Cells(TGSR.Rows.Count, "A").End(xlUp).row
        Set SrcDataRange = TGSR.Range("A2:A" & LastRow)
        Windows("JOB RECORD SHEET.xlsm").Visible = False
       
        Set Des = Workbooks("Automated Cardworker.xlsm")
        Set JCM = Des.Worksheets("Job Card Master")
        Set DesDataRange = JCM.Range("A2:Q299")
        Windows("Automated Cardworker.xlsm").Visible = True
        

    
    JCM.Range("A4").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 40, 0)
      Range("A4").Select

     SrcOpen.Close

            
     Application.ScreenUpdating = True
      
      End If


End Sub

question from:https://stackoverflow.com/questions/65848093/taking-a-value-from-other-workbook-but-vlookup-is-not-working

Answer 1

Waitting for answers