VBA to lookup value after pasting in anotherworkbook

[Steven101]

Ok, I have a macro that pastes data into a new or existing row in a different workbook.

The data goes into columns A:J and a formula works out the data in column K. I need to be able to get the data from that specific cell in Column K into a msgbox but I'm struggling to get to work. It's the code below that is not working, everything but the "myrange" part in the vlookup seems to work.

Any help would be appreciated.

Cheers, S

'lookup to check if case is pre or post check


 Dim myLookupValue As String
 Dim myColumnIndex As Long
 Dim myVLookupResult As String
 Dim myworksheetlookup As Worksheet
 Dim myrange As Range
 
 
 myLookupValue = Workbooks("Case Submission v2").Sheets("Data").Range("B2").Value
 Set myworksheetlookup = Workbooks("Submissions").Sheets("Submissions")
 Set myrange = myworksheetlookup.Range("B2:K1000")
 
 
 myVLookupResult = WorksheetFunction.VLookup(myLookupValue, myrange, myColumnIndex, False)


 MsgBox "This case has a check reason of: " & myVLookupResult

Sub CopyData()


'this copies data from input from Data sheet to specific MI folder
    Application.ScreenUpdating = False
    Dim wkbSource As Workbook
    Set wkbSource = ThisWorkbook
    Dim wbOpen As Workbook
    Dim sht As Worksheet, r As Long
    Set sht = wkbSource.Sheets("Data")
    Dim foundVal As Range
    'checks if ref number in column A is already on MI
    'change path and file name to suit your needs
    Set wbOpen = Workbooks.Open("\\ngclds07\retail3\Submissions.xlsm")
    Set foundVal = Range("B:B").Find(sht.Range("B2").Value, LookIn:=xlValues, lookat:=xlWhole)
    If Not foundVal Is Nothing Then
    ActiveWorkbook.Close True
    'duplicate
               MsgBox "This case has already been submitted, please check all details and amend. If correct please contact me"
    
                        
    Else
        r = wbOpen.Sheets("Submissions").Range("A" & Rows.Count).End(xlUp).Row + 1
        sht.Range("A2:J2").Copy
        wbOpen.Sheets("Submissions").Range("A" & r).PasteSpecial Paste:=xlPasteValues, Transpose:=False
        
        
'lookup to check if case is pre or post check


 Dim myLookupValue As String
 Dim myColumnIndex As Long
 Dim myVLookupResult As String
 Dim myworksheetlookup As Worksheet
 Dim myrange As Range
 
 
 myLookupValue = Workbooks("Case Submission v2").Sheets("Data").Range("B2").Value
 Set myworksheetlookup = Workbooks("Submissions").Sheets("Submissions")
 Set myrange = myworksheetlookup.Range("B2:K1000")
 
 
 myVLookupResult = WorksheetFunction.VLookup(myLookupValue, myrange, myColumnIndex, False)


 MsgBox "This case has a check reason of: " & myVLookupResult
      
 End If
    
    Sheets("Submit").Range("C7").Value = ""
    Sheets("Submit").Range("C9").Value = ""
    Sheets("Submit").Range("C11").Value = ""
    Sheets("Submit").Range("C13").Value = ""
    Sheets("Submit").Range("C15").Value = ""
    Sheets("Submit").Range("C17").Value = ""
    Sheets("Submit").Range("C19").Value = ""
    Sheets("Submit").Range("C21").Value = ""
    
    
    
    
    Application.CutCopyMode = False
    Application.ScreenUpdating = True

 

[Fluff]

You haven't assigned a value to "myColumnIndex"