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

 

[Darren Smith]

Please can someone help today

 

[Radoslaw Poprawski]

I would record a macro with a vlookup formula and then use that code in my own code.
i hope that advice will help a bit

 

[Joe4]

Please can someone help today

Sorry, I had out-of-town requests all weekend, so was off-line all weekend.
I cannot download files from my current computer, but will have access to later on today.

What I was really hoping for from you was a walk-through of a single example record, explaining what should happen for that particular example in each cell (with specific, like the cell addresses and values that are being updated).

 

[Darren Smith]

Any chance of a zoom call later because it`s a lot easier for me to show you rather than me trying to explain via forums massages

 

[Joe4]

There is an awful lot going on in that workbook, at lot of "noise" that is muddling the problem. I don't think all that is necessary.
It seems to me that the issue is that you may not be familiar with how VLOOKUP formulas work and need to be structured.

Did you look at the link on VLOOKUP that I provided last week, and at the examples provided?
What part of it is giving your trouble?

The arguments of the VLOOKUP function all work in unison.
Here are the 4 arguments (parts of the formula):

1. value
This is the value you are looking up (a single value, as you only look up one item at a time).

2. table
This is the range where you are looking for the value item.
Typically, this is a multi-column range. The first column is always the column where the "value" you are looking for resides.
Then you want to go out at least as far as the column that contains the value that you want to return when you find your match.

3. index_number
Of the columns that you have listed in the table (argument 2), this is the one that contains the value you want to return.

4. approximate_match
False - only return exact matches
True - return an approximate match, if an exact one is not found

So, let's say that you had a value in A1, and you wanted to look for the matching value in column M, and when you find the match, you want to return the value from column S, then your formula would look like:
=VLOOKUP(A1,M:S,7,FALSE)
(note that in the range of column M to column S, column S is the 7th column)

 

[Darren Smith]

I understand but I need to fill found data in a different workbook that`s the bit I can`t get my head around?
Please help

 

[Joe4]

I understand but I need to fill found data in a different workbook that`s the bit I can`t get my head around?

Once you get your result, you should be able to paste it anywhere you want.
It looks to me like you aren't even getting the correct result, as your VLOOKUP function is not structured correctly.

I usually do not recommend this, but since you have an awful lot going on in your workbook that I think is muddling up the waters, it may be beneficial to make a real simplified example of what you are trying to do for this one particular task in a new workbook (that does not have any other extraneous information, data, code, etc).

So if you can create a real simple example and show the part that you are having trouble with, maybe we can work through it.

 

[Darren Smith]

I tried to simplify my code by taking a lot away.
It seemed to start working as in some details transferred but now it just seems to go into a tailspin and no detail transfers??
Here is my new code

Private Sub Fill_Details_to_JobRecord_Click()

    TurnOff
                        Dim SrcOpen As Workbook
                        Dim JCM As Worksheet
                        Dim TGSR As Worksheet
                        Dim FilePath As String
                        Dim Filename As String
                        Dim iRow As Integer
                        Dim i As Long
                        Dim JobNo As String


                        Set JCM = ThisWorkbook.Worksheets("Job Card Master")


                     FilePath = "\\tgs-srv01\share\ShopFloor\PRODUCTION\JOB BOOK\"
                     Filename = "JOB RECORD SHEET.xlsm"
                     
                     Set SrcOpen = Workbooks.Open(FilePath & Filename)
                     Set TGSR = SrcOpen.Worksheets("TGS JOB RECORD")
                     
                     With JCM
                     iRow = .Range("S13:S" & .Rows.Count).Find("SELLING PRICE", LookIn:=xlValues, Lookat:=xlWhole).Row
                     iRow = iRow + 4
                     JobNo = JCM.Range("G2").Value
                     End With
                     
                     With TGSR
                     For i = 2 To Rows.Count
                     i = .Range("A13:A" & .Rows.Count).Find(JobNo, LookIn:=xlValues, Lookat:=xlWhole).Row
                    .Cells(i, 22) = JCM.Cells(iRow, 20)
                    .Cells(i, 23) = JCM.Cells(iRow + 11, 22)
                    .Cells(i, 24) = JCM.Cells(iRow + 13, 22)
                    .Cells(i, 25) = JCM.Cells(iRow + 15, 22)
                    .Cells(i, 26) = JCM.Cells(iRow + 2, 20)
                    .Cells(i, 27) = JCM.Cells(iRow + 11, 24)
                    .Cells(i, 28) = JCM.Cells(iRow + 13, 24)
                    .Cells(i, 29) = JCM.Cells(iRow + 15, 24)
                        Next i
                         End With
                     
                    SrcOpen.Close
TurnOn
End Sub

 

[Darren Smith]

Please could somebody help today

 

[NoSparks]

Can you define "today" ?
Local time for me is currently 3 AM

You should test your code by stepping through it one line at a time using the F8 key.

The For-Next loop on the TGSR sheet is endless as the variable i is being calculated to the same thing within each loop
so For i = 2 To Rows.Count the i will never be anything other than the calculated value and never get out of the loop.

Also most of the cells you copy are blank.