CODEWITHSUNDEEP
vbaexcel
IdunnoWut001
IdunnoWut001

Reputation: 13

VBA troubleshoot, copy one cell in one workbook to another cell in another workbook based on two criteria

I have two workbooks, one which is my main overview and another which is a trimmed down copy of my main workbook, which a colleague uses to write notes based on his contacts with the people listed in our shared lists.

I want to copy my colleagues notes into an appropriate column, by checking to see if the name and ID of the people are the same. So, I want to make sure the notes for Mike Smith, #12 get copied to his row and not the row for Mike Smith #77.

My thought was to do two loops, one that goes through each row of the main sheet and then for that particular main sheet row, loops through all the note sheet rows, looking for matches of ID and names, and when it finds a match for both, copies the notes in that row in the note sheet to the main sheet in the appropriate column.

Here's what I have:

Private Sub CommandButton1_Click()
Dim jbBook As Workbook

Dim x As Integer
Dim i As Integer

Set jbBook = Workbooks.Open("C:\...\noteWorkbook.xslx")

For i = 2 To 200
    For x = 2 To 200
        If Cells(i, 5) = jbBook.Worksheets(1).Cells(x, 5) Then If Cells(i, 16) = jbBook.Worksheets(1).Cells(x, 16) Then Cells(i, 52) = jbBook.Worksheets(1).Cells(i, 34)

    Next x
Next i

jbBook.Close

End Sub

Upvotes: 1

Views: 231

Answers (1)

FreeMan
FreeMan

Reputation: 5687

I believe the issue is with this:

If Cells(i, 5) = jbBook.Worksheets(1).Cells(x, 5) Then

Try changing it to 

If ActiveWorkbook.Cells(i, 5) = jbBook.Worksheets(1).Cells(x, 5) Then

I believe that the unreferenced Cells() is what's giving the error. You would, of course, have to change all of them.

You may also consider breaking the If/Thens onto separate lines, just for readability.

Upvotes: 1

Related Questions