Copying from one workbook to another

[jamessugrono]

Hey,
I'm kind of a novice when it comes to VB, but I pick up lots of little tricks here and there that I tend to find useful.

At the moment, I'm working on a project where I need to read names from (a specified column in a sheet in) one workbook, find the name in (another specified column in another sheet in) another workbook, then paste data from another column in that corresponding row (in the second workbook).

Hmm. It made a lot more sense in my mind.

Okay, so it's looking a little like this at the moment:

Private Sub Worksheet_BeforeRightClick(ByVal Target As Range, Cancel As Boolean)
Cancel = True
'Some variables
    Dim agentName As String
    Dim searchName As String
    Dim currentRow As Long
    Dim searchRow As Integer
    Dim maxBlanks As Integer
    Dim blankRows As Integer
    Dim mainSheet As Worksheet
    sheetname = "Sheet1"
    Set mainSheet = Workbooks("stats prototype.xlsm").Worksheets(sheetname)
    
blankRows = 0
maxBlanks = 10
currentRow = 1
searchRow = 1
agentName = ""


Do While blankRows < maxBlanks
    agentName = mainSheet.Cells(currentRow, 1)
    If agentName = "" Then
        blankRows = blankRows + 1
    ElseIf agentName <> "Names" Then
        Do While searchBlanks < maxBlanks
            'This is where I plan to include a search in a second workbook, in a second sheet
            'for the row containing agentName in the first column and then copy from another column
            'in that row to a cell in mainSheet
        Loop
    blankRows = 0
    End If
    
    currentRow = currentRow + 1

Loop


End Sub

So pretty much, I need a way to search in another workbook (which can be open, if necessary) for agentName and then copy it across.