VBA to lookup value after pasting in anotherworkbook

Steven101

Board Regular
Joined
Oct 27, 2014
Messages
62
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

Code:
'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
Code:
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
 

Excel Facts

Spell Check in Excel
Press F7 to start spell check in Excel. Be careful, by default, Excel does not check Capitalized Werds (whoops)
You haven't assigned a value to "myColumnIndex"
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,953
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top