Thread: copy from variable field location to variable field location

Hello, and Hello again to everyone.

I was wondering if anyone could help me with a little project I have going on.
I'm trying to copy date paid and check numbers from book1 to book2 that have matching invoice numbers.
My first problem is that my first invoice field location is variable, after that it progresses sequentially.
The second is that the row of my target fields is also variable.

Rather than explain I thought it would be better to list out my steps and include a dummy file. The field locations I've listed below are just examples and do not always remain the same unless listed as static.
Copy first (Invoice) field from book1 location(5,C)(variable, variable)
Switch Focus to book2
CTRL+F
Paste (Invoice 5C) into find field
Press Find
Invoice number is found at field location (78J)(Variable,Static)
Switch Focus To book 1
Copy (date paid) and (check number) from fields (5,D) & (5,E)
Switch Focus to book 2
Paste (date paid) and (check number) into (78,N) and (78,O) (variable,static) (variable, static)
Switch focus to book1
Copy second (Invoice) field from book1 (6C)(first invoice field location + 1 row)
Switch focus to book2
Paste (Invoice6C) into find field
Press Find
Invoice number is found at field (34J)
Switch Focus to book1
Copy (date paid) and (check number) from fields (6D) & (6E)
Switch focus to book2
Paste (date paid) and (check number) into (34N) and (34O)
Switch focus to book1
Copy third (Invoice) field from book1 (7C)(first invoice field location +2 row)
and so on........


Repeat until out of Invoice numbers

One final consideration, … From time to time there are invoice numbers from book1 that don't match to anything in book2. There are in fact matches but due to input error or other, I have to go back and manually find them. This is perfectly okay, but I want to make sure the macro doesn't fail out when it finds one that doesn't match, instead if it could just close the "we didn't find what you were looking for" dialog, shift focus back to book1 and highlight the invoice in book1 that it couldn't find in book2 and move on, that would be PERFECT!

I'm sorry if any of this is confusing or if I'm not using correct terminology, I'm a total newbie when it comes to excel. I've added a dummy file as well in case that helps.

Thanks so much in advance for any help.

This macro will do it. Put the macro into the Master workbook and save as a macro-enabled workbook. The macro will prompt you to pick the Update file from your hard drive, it will open it and run the update.

If all invoices are found, it will tell you so and close the Update workbook.
If all invoices are not found, it will highlight the ones it did not find and leave the workbook open for review.

Option Explicit

Sub UpdatePayments()
Dim ws1 As Worksheet
Dim wbName As String
Dim wb2 As Workbook
Dim ws2 As Worksheet
Dim InvRNG As Range
Dim Invoice As Range
Dim InvFND As Range
Dim BadCnt As Long

wbName = Application.GetOpenFilename("Microsoft Office Excel Files (.xlsx),.xlsx")
If wbName = "False" Then Exit Sub

Application.ScreenUpdating = False
Set ws1 = ThisWorkbook.Sheets("Sheet1")
Set wb2 = Workbooks.Open(wbName)
Set ws2 = wb2.Sheets("Sheet1")

Set InvRNG = ws2.Range("C:C").SpecialCells(xlConstants, xlNumbers)
On Error Resume Next

For Each Invoice In InvRNG
    Set InvFND = ws1.Range("J:J").Find(Invoice, LookIn:=xlValues, LookAt:=xlWhole)
    If Not InvFND Is Nothing Then
        ws1.Range("N" & InvFND.Row).Resize(, 2).Value = Invoice.Offset(, 1).Resize(, 2).Value
        Set InvFND = Nothing
    Else
        Invoice.Interior.ColorIndex = 3
        BadCnt = BadCnt + 1
    End If
Next Invoice
        
If BadCnt > 0 Then
    MsgBox "There were " & BadCnt & " invoices not found. They have been highlighted for reference."
    wb2.Activate
    ws2.Activate
Else
    MsgBox "All invoices were found and updated."
    wb2.Close False
End If

Application.ScreenUpdating = True
End Sub

Glad to help, of course. You should peruse some new threads and look for questions you might be able to answer for another OP.

If that takes care of your need, please click EDIT in your original post, click GO ADVANCED and set the PREFIX box to SOLVED.