Thread: Update cell values in master file if changes were made in update file

I have a file which is comprised of 4 different sheets
(1) FullFile; contains ~40,000 records
(2) RR-1; research report 1
(3) RR-2; research report 2
(4) RR-2; research report 3

The research reports tabs contain updated information for records in the FullFile. For each record in each research report, I want to update the FullFile with the updated information. I was going to do something where for each record in FullFile, look to see if records exists in research report 1, 2, and 3. If record found, then update fields accordingly, else (not found) do nothing. The issue with this is the processing time is going to take a long time to complete. Is there a more efficient way to accomplish this? I have attached small sample file if needed.

An example of an update in the file would be for record number 42, for example. Record number 42 is the first record in research report 1. Once a record is located in any research report, I need to update all three fields lastname, firstname, and mailing address in the FullFile. Thanks

Personally, I'd process each RR sheet and, within that, process each record. You have an ID so it would be easy to match the RR Id with the Full File ID. And, for each RR record, if a field is not blank, replace it in the Full File entry.

That would be much faster than processing the entire Full File sheet.

The pseudo code would be something like:

For each sht in sheets
  If left(sht.name,2) = "RR" then
    for each row in sht
      locate fullfile record
      compare and replace field 1
      compare and replace field 2
      compare and replace field 3
    next row
  End If
next sht

Regards, TMS

OK, try:

Sub UpdateMaster()

Dim lLR As Long
Dim wSht As Worksheet
Dim rCell As Range
Dim awf As WorksheetFunction: Set awf = WorksheetFunction
Dim lMatch As Long

With Sheets("FullFile")
    lLR = .Range("A" & .Rows.Count).End(xlUp).Row
End With

For Each wSht In Sheets
    If Left(wSht.Name, 2) = "RR" Then
        With wSht
            For Each rCell In .Range("A2:A" & .Range("A" & .Rows.Count).End(xlUp).Row)
                lMatch = awf.Match(rCell.Value, Sheets("FullFile").Range("A1:A" & lLR), 0)
                'Debug.Print wSht.Name & " " & rCell & " " & lMatch
                rCell.Offset(0, 8).Resize(1, 3).Copy Sheets("FullFile").Range("E" & lMatch)
                'Sheets("FullFile").Range("J" & lMatch) = "updated " & wSht.Name & " " & rCell
            Next rCell
        End With
    End If
Next 'wSht
            
End Sub

Please test on a copy of your file.

Note that I included a line of code to flag column J with the source of the changes. It's commented out above but it might be useful, at least for your testing purposes.


Regards, TMS

You are very welcome. Thanks for the rep.

Regards, TMS