Update a Master Loan Workbook and Add New Rows with Excel VBA
Question details
Compare a monthly loan file with a master workbook, update changed interest rates, and add records for new manufacturers.

- Product
- Microsoft Excel VBA
- Device & OS
- not provided
- Scenario
- A monthly loan file must be merged into an existing master workbook using manufacturer names as record keys.
- Observed behavior
- Existing records should retain unchanged values, changed interest rates should be updated, and new manufacturers should be appended.
Confirm that manufacturer names are unique in each file and that columns A, B, and C contain manufacturer, loan amount, and interest rate, with headers in row 1. Ensure names are text and interest rates are numeric, then open both workbooks in desktop Excel for Windows; the example uses Scripting.Dictionary.
Run a VBA macro to update rates and append manufacturers
Use this repeatable approach when the monthly and master sheets share the same three-column layout.
The macro matches trimmed manufacturer names without regard to letter case. For existing manufacturers, it updates column C only; column B remains unchanged. For new manufacturers, it appends values from columns A through C.
The macro stops before making changes if it finds duplicate nonblank manufacturer names in either sheet. It skips rows with blank manufacturer names.
Save the master workbook as an Excel Macro-Enabled Workbook (.xlsm) using File > Save As. Name its data sheet Master, open the monthly file as Monthly.xlsx, and name its data sheet Monthly. Change the workbook and sheet names in the code if yours differ.
In the master workbook, press Alt+F11, select Insert > Module, and paste this code: Sub UpdateMasterLoans() Dim master As Worksheet, monthly As Worksheet Dim masterKeys As Object, sourceKeys As Object Dim lastMaster As Long, lastSource As Long Dim r As Long, targetRow As Long, key As String Set master = ThisWorkbook.Worksheets("Master") Set monthly = Workbooks("Monthly.xlsx").Worksheets("Monthly") Set masterKeys = CreateObject("Scripting.Dictionary") Set sourceKeys = CreateObject("Scripting.Dictionary") masterKeys.CompareMode = vbTextCompare sourceKeys.CompareMode = vbTextCompare lastMaster = master.Cells(master.Rows.Count, "A").End(xlUp).Row lastSource = monthly.Cells(monthly.Rows.Count, "A").End(xlUp).Row For r = 2 To lastMaster key = Trim(CStr(master.Cells(r, "A").Value)) If Len(key) > 0 Then If masterKeys.Exists(key) Then MsgBox "Duplicate master manufacturer: " & key Exit Sub End If masterKeys.Add key, r End If Next r For r = 2 To lastSource key = Trim(CStr(monthly.Cells(r, "A").Value)) If Len(key) > 0 Then If sourceKeys.Exists(key) Then MsgBox "Duplicate monthly manufacturer: " & key Exit Sub End If sourceKeys.Add key, r End If Next r For r = 2 To lastSource key = Trim(CStr(monthly.Cells(r, "A").Value)) If Len(key) > 0 Then If masterKeys.Exists(key) Then targetRow = masterKeys(key) If master.Cells(targetRow, "C").Value2 <> monthly.Cells(r, "C").Value2 Then master.Cells(targetRow, "C").Value2 = monthly.Cells(r, "C").Value2 End If Else lastMaster = lastMaster + 1 master.Range("A" & lastMaster & ":C" & lastMaster).Value = monthly.Range("A" & r & ":C" & r).Value End If End If Next r MsgBox "Master loan records updated." End Sub
Return to Excel, select Developer > Macros, choose UpdateMasterLoans, and select Run. Review the Master sheet, apply the appropriate number and percentage formats to newly added rows, and save the master workbook.

Review Loan Workbooks with WPS Spreadsheets
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface and support for Excel formats, making it easy to move and review loan workbooks. Excel VBA macro support can vary by WPS edition, so verify compatibility before relying on an existing macro.

Frequently Asked Questions
What happens if a manufacturer appears more than once?
The macro displays a duplicate-name message and stops before updating the master. Review column A in the indicated sheet and resolve the duplicate before running Developer > Macros again.
Does the macro update loan amounts for existing manufacturers?
No. It updates only the interest rate in column C for existing manufacturers. Column B is copied from the monthly file only when a manufacturer is new.
Why does the macro say it cannot find the monthly workbook?
Open the monthly file in Excel and check that its workbook name matches Monthly.xlsx in the code. Also check that its worksheet is named Monthly, or edit the corresponding names in the VBA module.
Can I use Remove Duplicates instead of VBA?
For a simple one-time merge, you can combine the rows and use Data > Remove Duplicates on the manufacturer column. Check which record Excel retains: this method does not independently compare and update interest rates.




