Copying from one workbook to another

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:

Code:

---


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.

welcome to the forum,
this is an example of just copy from another closed workbook

Code:

---

Dim Wb1 As Workbook
Application.ScreenUpdating = False
Set Wb1 = Workbooks.Open("C:\sample.xls")

Wb1.Sheets(1).Range("A1:A10").Copy
Wb1.Close SaveChanges:=False

ThisWorkbook.Sheets(1).Range("a1").PasteSpecial Paste:=xlPasteAll
Application.ScreenUpdating = True

---

i have asked for very simple thing with all possible code but didn't receive proper reply. may I ask for your help here please: http://www.vbforums.com/showthread.php?t=656504

Sarah, welcome to the forum, u got a reply there from westconn1, but why dont u reply to that post?