Darren Smith
Well-known Member
- Joined
- Nov 23, 2020
- Messages
- 631
- Office Version
- 2019
- Platform
- Windows
This code does work but when i try to use listbox4 Fill Details command it does not activate. Can anyone say why???
VBA Code:
Private Sub ListBox4_Click()
If 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
FilePath = "\\TGS-SRV01\Share\ShopFloor\PRODUCTION\JOB BOOK\"
Filename = "JOB RECORD SHEET.xlsm"
Set Des = Workbooks("Automated Cardworker.xlsm")
Set JCM = Worksheets("Job Card Master")
Set SrcOpen = Workbooks.Open(FilePath & Filename)
Set TGSR = SrcOpen.Worksheets("TGS JOB RECORD")
Set SrcDataRange = TGSR.Range("A2:AN9000")
Set DesDataRange = JCM.Range("A2:Q299")
JCM.Range("A4").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 40, 0)
Range("A4").Select
JCM.Range("C4").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 8, 0)
Range("C4").Select
JCM.Range("D4").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 33, 0)
Range("D4").Select
JCM.Range("F6").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 18, 0)
Range("F6").Select
JCM.Range("A8").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 2, 0)
Range("A8").Select
JCM.Range("C8").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 3, 0)
Range("C8").Select
JCM.Range("G8").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 5, 0)
Range("G8").Select
JCM.Range("K10").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 7, 0)
Range("K10").Select
JCM.Range("K8").Value = Application.WorksheetFunction.VLookup(JCM.Range("G2"), SrcDataRange, 4, 0)
Range("K8").Select
SrcOpen.Close
Application.ScreenUpdating = True
End If
End Sub
