I need to fill details to another workbook

[Darren Smith]

I am trying to fill in details to another workbook but I get error 1004 Unable to get the VLookup property of the worksheet function class.
How can I sort this error?

Private Sub Fill_Details_to_JobRecord_Click()

    TurnOff
               
                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 iRow As Integer
                        
                        
                        FilePath = "\\tgs-srv01\share\ShopFloor\PRODUCTION\JOB BOOK\"
                        Filename = "JOB RECORD SHEET.xlsm"
                        
                        Set JCM = ThisWorkbook.Worksheets("Job Card Master")

                        Set SrcDataRange = JCM.Range("A13").CurrentRegion

                        
'                      On Error GoTo ErrHandler
                     With JCM
                    
                     iRow = .Range("S13:S" & .Rows.Count).Find("SELLING PRICE", LookIn:=xlValues, lookat:=xlWhole).Row
                     iRow = iRow + 4

                     End With
                     
                     Set SrcOpen = Workbooks.Open(FilePath & Filename)
                     Set TGSR = SrcOpen.Worksheets("TGS JOB RECORD")
                     Windows("JOB RECORD SHEET.xlsm").Visible = True
                     
                      TGSR.Range("V").Value = WorksheetFunction.VLookup(JCM.Range("G2"), iRow, 20, 0)
                      Range("V").Select
                      
                      TGSR.Range("W").Value = WorksheetFunction.VLookup(JCM.Range("G2"), iRow + 16, 22, 0)
                      Range("W").Select
                      
                      TGSR.Range("X").Value = WorksheetFunction.VLookup(JCM.Range("G2"), iRow + 18, 22, 0)
                      Range("X").Select
                      
                      TGSR.Range("Y").Value = WorksheetFunction.VLookup(JCM.Range("G2"), iRow + 20, 22, 0)
                      Range("Y").Select
                      
                      TGSR.Range("Z").Value = WorksheetFunction.VLookup(JCM.Range("G2"), iRow + 2, 22, 0)
                      Range("Z").Select
                      
                      TGSR.Range("AA").Value = WorksheetFunction.VLookup(JCM.Range("G2"), iRow + 16, 24, 0)
                      Range("AA").Select
                      
                      TGSR.Range("AB").Value = WorksheetFunction.VLookup(JCM.Range("G2"), iRow + 18, 24, 0)
                      Range("AB").Select
                     
                      TGSR.Range("AB").Value = WorksheetFunction.VLookup(JCM.Range("G2"), iRow + 20, 24, 0)
                      Range("AB").Select
                    
                      SrcOpen.Close
         
'ErrHandler:
'                       If Err = 1004 Then
'                       MsgBox "Fill Job Number in Job Card Master Sheet Cell G2"
'                       End If

TurnOn
End Sub

 

[Joe4]

Your VLOOKUP functions are not formulated properly.
The second argument is supposed to be a range value, the range to look in for the match (see: MS Excel: How to use the VLOOKUP Function (WS))
You are using a row number (iRow) for that argument:

TGSR.Range("V").Value = WorksheetFunction.VLookup(JCM.Range("G2"), iRow, 20, 0)

That makes no sense.

 

[Darren Smith]

Sorry to be thick but how can you find the row then find the value in rows & columns below using VLookup?

 

[Joe4]

Please walk me through an actual example of what you are trying to do.

 

[Darren Smith]

I am trying to find a value in a column then drop down some rows in the specified rows & columns.
Then copy the found value to another workbook in the specified column.
Using the value in cell G2 in the Job Card Master worksheet to find the right row in the destination worksheet.
Does that help?

 

[Joe4]

I am trying to find a value in a column then drop down some rows in the specified rows & columns.
Then copy the found value to another workbook in the specified column.
Using the value in cell G2 in the Job Card Master worksheet to find the right row in the destination worksheet.
Does that help?

No, I want you to walk us through an actual example, in detail, referencing exact range references along the way.
I need to understand how you want this to work, so I need to know all the details. Walking us through an actual example may help shed some light on that.

 

[Darren Smith]

Ok I will try to explain
1. You press the command button (Fill Detail to Job Record) on the user form.
2. The code opens the Job Record workbook.
3. It should find cells in Job Card Master Sheet Cells are T166, T168, X176, X178, X180. But this will vary in rows but not columns. This is why I am using the Find Method
4. Then after that fill details into the Job Record Sheet.
5. With the TGS Job Record sheet The value in a row in Column A needs to match the value in G3 in the Job Car Master Sheet
6. TGS Job Record Sheet Column V = T166 in Job Card Master sheet
7. TGS Job Record Sheet Column W = T176 in Job Card Master sheet
8. TGS Job Record Sheet Column X = T178 in Job Card Master sheet
9. TGS Job Record Sheet Column Y = T180 in Job Card Master sheet
10. TGS Job Record Sheet Column Z = X176 in Job Card Master sheet
11. TGS Job Record Sheet Column AA = X176 in Job Card Master sheet
12. TGS Job Record Sheet Column AB = X178 in Job Card Master sheet
13. TGS Job Record Sheet Column AC = X180 in Job Card Master sheet
14 . Then Close JobRecordSheet workbook

Also, put my workbook below

Transfer - Dropbox

Dropbox is a free service that lets you bring your photos, docs, and videos anywhere and share them easily. Never email yourself a file again!

www.dropbox.com

 

[Darren Smith]

Please can somebody help with this today

 

[NoSparks]

Are you sure

Set SrcDataRange = JCM.Range("A13").CurrentRegion

is what/where you think it is ?


Using the Immediate window, type this in and see

ThisWorkbook.Worksheets("Job Card Master").Range("A13").CurrentRegion.Select

Personally,
I'd try eliminating the use of row numbers and the VLookups by setting a range variable on both the "Job Card Master" and "TGS JOB RECORD" sheets and working from each of them using .Offset

 

[Darren Smith]

Please can you do an example of what you mean sorry don`t quite understand?