Can't seem to get the ranges to work

[Darren Smith]

This code has 2 sets of ranges both don't seem to work? The SrcDataRange & DesDataRange
One is Destination workbook other is Source workbook?

Sub Job_Record_Detail()

Application.ScreenUpdating = False
 


    Dim Src As Workbook
    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 Src = Workbooks("JOB RECORD SHEET.xlsm")
    Set TGSR = Worksheets("TGS JOB RECORD")
 
    Set SrcDataRange = Workbooks(Src).Worksheets(TGSR).Range("A2:NA").Select
 
    Set DesDataRange = Workbooks(Des).Worksheets(JCM).Range("A2:Q").Select

    DesDataRange("A4").Value = Application.WorksheetFunction.VLookup(DesDataRange("G4"), Range("SrcDataRange"), 40, 0)
    Columns("A:Q").EntireColumn.AutoFit
    Range("A4").Select


         
    Src.Close
         
     Application.ScreenUpdating = True
         
         
 
    End Sub

 

[Joe4]

Two things about your range declarations:

1. Get rid of the ".Select" at the end of your range declarations.
You are just defining them here, not selecting them.

2. If you are including starting row, you need an ending row too.
Otherwise, choose the whole column.

So change:

    Set SrcDataRange = Workbooks(Src).Worksheets(TGSR).Range("A2:NA").Select
   
    Set DesDataRange = Workbooks(Des).Worksheets(JCM).Range("A2:Q").Select

to something like this:

    Set SrcDataRange = Workbooks(Src).Worksheets(TGSR).Range("A:NA")
   
    Set DesDataRange = Workbooks(Des).Worksheets(JCM).Range("A:Q")

 

[mart37]

Set DesDataRange = Workbooks(Des).Worksheets(JCM).Range("A2:Q?????").Select

 

[Fluff]

Further to has already been said it should be

    Set Des = Workbooks("Automated Cardworker.xlsm")
    Set JCM = Des.Worksheets("Job Card Master")
    Set SrcOpen = Workbooks.Open(FilePath & Filename)
    Set TGSR = SrcOpen.Worksheets("TGS JOB RECORD")
 
    Set SrcDataRange = TGSR.Range("A2:NA??")
 
    Set DesDataRange = JCM.Range("A2:Q??")

 

[MARK858]

As a guess...

With Workbooks(Src).Worksheets(TGSR)
   Set SrcDataRange = .Range("A2:NA" & .Columns("A:NA").Find("*", , xlValues, , xlByRows, xlPrevious).Row)
End With

With Workbooks(Des).Worksheets(JCM)
    Set DesDataRange = .Range("A2:Q" & .Columns("A:Q").Find("*", , xlValues, , xlByRows, xlPrevious).Row)
End With

 

[Darren Smith]

Thanks for your help everyone

This Vlookup in the same code is not working please help,

DesDataRange("A4").Value = Application.WorksheetFunction.VLookup(DesDataRange("G4"), Range("SrcDataRange"), 40, 0)

Thanks in advance

 

[Norie]

Darren

Can you explain, in words, what you are trying to do?

In this code Range("SrcDataRange") is definitely wrong, it should be SrcDataRange, but It's not really clear what you are trying to refer to with DesDataRange("A4") and DesDataRange("G4").

As it is they would refer to the 1st column and 4th row of the range DesDataRange and the 7th column and 4th row of the range DesDataRange respectively.

 

[Darren Smith]

Darren

Can you explain, in words, what you are trying to do?

In this code Range("SrcDataRange") is definitely wrong, it should be SrcDataRange, but It's not really clear what you are trying to refer to with DesDataRange("A4") and DesDataRange("G4"). Sorry should of been ("G2")
As it is they would refer to the 1st column and 4th row of the range DesDataRange and the 7th column and 4th row of the range DesDataRange respectively.

I am trying to take value in Destination Workbook Sheet Job Card Master in cell G2 the find corresponding value in the A column in the Source workbook Sheet TGS JOB RECORD then vlookup value in the NV Column.
Then add value from Source Workbook Worksheet to Cell A4 in Destination Workbook Sheet Job Card Master.

 

[Fluff]

Are you trying to return the value from column NV?
You data range only goes up to NA & col 40 in your vlookup is column AN?

 

[Darren Smith]

Sorry yes the return value is in AN column