Microsoft Excel Macro for Copying Data from Another Workbook Based on Criteria

[johnellis1971200]

Workbook 1 has data in cell A3 and J3.

Likewise, Workbook 2 has data in cells A2 and B2.

I need to devise (or modify within a VBA script) a macro to copy data from A2 in Workbook 2 to A3 in Workbook 1, as long as cell B2 in Workbook 2 matches cell J3 in Workbook 1. 

To give a little more background, column A in both workbooks is a document number.  Column B in Workbook 2 and column J in Workbook 1 is an Employee ID.

Workbook 1 has "Document No.", while Workbook 2 has "No.". 

Likewise, Workbook 1 has "Payroll Employee No." and Workbook 2 has "Employee No.".

I want to emphasize that these are two separate workbooks -- not two separate spreadsheets on two tabs of the same workbook.

Someone gave me the code below.  But, it's not working.  I'm getting syntax errors.

Would someone please help me fix this code?

Thanks!

Sub CopyDataBetweenWorkbooks()
    ' Declare variables
    Dim sourceWorkbook As Workbook, targetWorkbook As Workbook
    Dim sourceSheet As Worksheet, targetSheet As Worksheet
    Dim No. As String, Employee No. As String
    Dim matchCell As Range, lastRowSource As Long, lastRowTarget As Long


    ' Set references to source and target workbooks
    Set sourceWorkbook = Workbook("Payroll Processing.xlsx")
    Set targetWorkbook = Workbook("Job Journals.xlsx")


    ' Set references to source and target sheets
    Set sourceSheet = sourceWorkbook.Sheets(1)
    Set targetSheet = targetWorkbook.Sheets(1)


    ' Find the last row with data in column A of both workbooks
    lastRowSource = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
    lastRowTarget = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row


    ' Loop through each row in the source workbook
    For i = 2 To lastRowSource
        ' Get document number and employee ID from source workbook
        No. = sourceSheet.Cells(i, 1).Value
        Employee No. = sourceSheet.Cells(i, 2).Value


        ' Find the matching row in the target workbook based on employee ID
        Set matchCell = targetSheet.Columns(2).Find(Employee No., LookIn:=xlValues, LookAt:=xlWhole)


        ' If a match is found, copy data from source to target workbook
        If Not matchCell Is Nothing Then targetSheet.Cells(matchCell.Row, 1).Value = No.
    Next i
End Sub

[#2 · Accepted solution]

@johnellis1971200 

1) Variable names cannot contain spaces and periods.

2) Workbook(...) should be Workbooks(...)

3) The variable i has not been declared.

Try this version (I haven't tested it):

Sub CopyDataBetweenWorkbooks()
    ' Declare variables
    Dim sourceWorkbook As Workbook, targetWorkbook As Workbook
    Dim sourceSheet As Worksheet, targetSheet As Worksheet
    Dim No As String, EmployeeNo As String
    Dim matchCell As Range, lastRowSource As Long, lastRowTarget As Long
    Dim i As Long

    ' Set references to source and target workbooks
    Set sourceWorkbook = Workbooks("Payroll Processing.xlsx")
    Set targetWorkbook = Workbooks("Job Journals.xlsx")

    ' Set references to source and target sheets
    Set sourceSheet = sourceWorkbook.Sheets(1)
    Set targetSheet = targetWorkbook.Sheets(1)

    ' Find the last row with data in column A of both workbooks
    lastRowSource = sourceSheet.Cells(sourceSheet.Rows.Count, "A").End(xlUp).Row
    lastRowTarget = targetSheet.Cells(targetSheet.Rows.Count, "A").End(xlUp).Row

    ' Loop through each row in the source workbook
    For i = 2 To lastRowSource
        ' Get document number and employee ID from source workbook
        No = sourceSheet.Cells(i, 1).Value
        EmployeeNo = sourceSheet.Cells(i, 2).Value

        ' Find the matching row in the target workbook based on employee ID
        Set matchCell = targetSheet.Columns(2).Find(EmployeeNo, LookIn:=xlValues, LookAt:=xlWhole)

        ' If a match is found, copy data from source to target workbook
        If Not matchCell Is Nothing Then targetSheet.Cells(matchCell.Row, 1).Value = No
    Next i
End Sub

[#3]

@HansVogelaar 

Thanks, but I receive the following error upon running this:

[#4]

@johnellis1971200 

Click anywhere in the code.

Press repeatedly F8 to execute the macro line by line.

At which line of the code does the error pop up?

[#5]

I got it to work! I just had to change targetsheet.Columns(2) to targetsheet.Columns(10). 🙂

Thanks!