Need to take detail from a workbook using a vlookup function

[Darren Smith]

When i click the list box on "Fill Details" nothing happens???

    If ListBox4 = "Fill Details" Then
            
    On Error GoTo ErrHandler
    Application.ScreenUpdating = False
    
    Dim src As Workbook
    Dim JCM As Worksheet
    
    
    Set src = Workbooks.Open("\\TGS-SRV01\Share\ShopFloor\PRODUCTION\JOB BOOK\JOB RECORD SHEET.xlsm", True, True)
    Set JCM = Worksheets("Job Card Master")
    
    Dim iTotalRows As Integer
    iTotalRows = src.Worksheets("sheet1").Range("A1:A" & Cells(rows.Count, "A").End(xlUp).row).rows.Count
   
   
    Dim iCnt As Integer
    For iCnt = 2 To iTotalRows
        Sheet1.Cells("40" & iCnt) = Application.WorksheetFunction.VLookup(JCM.Cells("G2"), _
        Sheets(JCM).Range("G2"), iCnt, 0)
    Next iCnt
    

    src.Close False
    Set src = Nothing
    
ErrHandler:
    Application.EnableEvents = True
    Application.ScreenUpdating = True

MsgBox "Successfully entered Job Book data to Job Card Master Headers"

End If 

End sub

 

[Candyman8019]

You’ll need a method to trigger the code to run. For example a worksheet change event by adding the following to the start of your VNA code.
Private Sub Worksheet_Change(ByVal Target As Range)

 

[Darren Smith]

Hi

Tried what you said but it jumps over all the coding?

 

[Candyman8019]

Safe to assume that you’re putting this code in the particular sheet it pertains to rather than ThisWorkbook?

 

[Darren Smith]

Morning,

Yes that`s correct

 

[Candyman8019]

Perhaps this section needs to be updated...try the following:

For iCnt = 2 To iTotalRows
Worksheets("Sheet1").Cells("40" & iCnt) = Application.WorksheetFunction.VLookup(JCM.Cells("G2"), _
Worksheets(JCM).Range("G2"), iCnt, 0)
Next iCnt

 

[Darren Smith]

Seems to not do anything when you press Fill Details in listbox?

 

[Candyman8019]

Is this saved as a macro enabled workbook? .xlsm

 

[Darren Smith]

Yes it is

 

[Darren Smith]

Please help me with this.