In this post we see how to compare two Excel workbooks with VBA macros. The macro checks if the number of worksheets matches, then compares the worksheet names and the contents in the sheets with the same name (this last piece of code has been covered in this other post: Compare Two Worksheets). Whenever there is a mismatch, it highlights the sheet or cells with different values in yellow, and displays a message with cell and worksheet (this could also be copied to a separate sheet instead). Note that the macro compares the workbook with the macro (ThisWorkbook) and a second open workbook. Optionally, a dialog to open the second file, or even the two workbooks could be implemented at the start if need be.
Macro code:
Macro explained:
- We declare object variables to assign each of the two workbooks to (wb1, wb2), and for respective worksheets in each workbook (ws1, ws2). We also declare a Boolean to store the status of the worksheets match (wsMatch).
- Then we define wb1 as the workbook with the macro (ThisWorkbook), and wb2 as the second open workbook. This assumes the workbook with the macro was open first, and the other workbook second if there more workbooks open. Otherwise, it is better to use the exact workbook name instead of the index. Additionally, a file dialog could be used to select the second workbook, or even both workbooks to be compared (the macro would be then standing alone in a third workbook).
- Now we check if the two workbooks have the same number of worksheets to progress with the macro. This step can be omitted if you want to compare the workbooks anyway regardless of having the same number of worksheets or not.
- The first For loop goes through each worksheet in the first workbook. Inside that loop, we have a second nested For loop that goes through the worksheets (ws2) in the second workbook for each worksheet (ws1) in the first workbook, in order to find the worksheet with the same name. Note that we set wsMatch to False for every worksheet in the first workbook.
- If there is a match, i.e. there is a worksheet with the same name, the Boolean wsMatch is set to True, and we start another routine or loop to compare the contents of the two worksheets. That has been covered in this other post: Compare Two Worksheets, so check that out for more details. Any different values are highlighted in yellow, and a message box displays the cell address and worksheet name. This can be omitted if you expect many changes; or it can be added to other sheet instead of displaying the message (the message box interrupts the code execution each time).
- If a match is not found, i.e. there is not a worksheet with that name, the Boolean wsMatch remains False and then we highlight the tab of that worksheet in yellow.
This is how we compare two workbooks in Excel using VBA macros.
Other examples:
- How to Copy/Paste Charts To PowerPoint with Excel VBA macros
- How to Update Web Query On File Open with Excel VBA macros
- How to Insert Picture From Google Drive with Excel VBA macros
This is an outstanding macro. I have an issue though. I have two retorts I pulled. Wb1 is report 1 and wb2 is report 2. I am comparing two workbooks but wb2 might have additional rows so no matter what those cells will mismatch. How do I compare specific values in a cell in wb1 to those values in wb2 to find out if they changed?
ReplyDeleteYou should then only compare two worksheets (just part of the code). It is also explained in this other post: https://excelmacroclass.blogspot.com/2021/12/compare-two-worksheets.html
DeleteNow, in your case, you have a worksheet in one workbook, and the other worksheet in another workbook, so just need to reference each workbook wb1 and wb2. I tried to explain that better in another video series on the YouTube channel: Compare Everything in Excel. You should have a look at Part 2: Compare worksheets. Here's the link to the video: https://youtu.be/T4QMCoFlVdc
I understand your report 2 has additional rows and you don't want to highlight those as differences. For that, in the loop to compare 2 worksheets you just loop through cells in wb1.ws1, as in the example above.
Hi this is a great macro. I have a question for my scenario. I pull two workbooks each containing two sheets everyday. I compare both workbooks with the previous day's workbooks and find difference and then highlight it. How can I edit this macro to compare only specific rows having a certain word value from today's workbooks against yesterday's workbook for all the sheets ? Thanks in advance.
ReplyDeleteYou can add another condition inside the For Each cell loop if you want to keep doing a cell-to-cell comparison (other possibility is just looping through rows). If a cell has your "word" value, then you continue with the other condition to highlight or copy that cell or row when different. Something like this:
ReplyDeleteFor Each cell In ...
If cell.Value = "word" Then
If cell.Value <> ws2.Range(cell.Address).Value Then
cell.Interior.Color = vbYellow
MsgBox "Mismatch cell " & cell.Address & " in worksheet " & ws1.Name
End If
End If
Next cell
Thanks for your response! After adding in that line, only the cells with that "word" in worksheet1 are getting highlighted, the ones in ws2 are not. How can I solve this ? After that, how do I incorporate finding the differences between these rows with the specific "word" among both the workbooks and then export those rows(which make up difference) into a new excel file ?
ReplyDeleteThanks in advance for the help
The condition was only for ws1 (cell.Value). You can add another condition for the same cell in ws2, that's ws2.range(cell.Address).Value. The condition then would be:
ReplyDeleteIf cell.Value = "word" Or ws2.range(cell.Address).Value = "word' Then
'code to check difference between ws1 and ws2 for that cell here
To save the difference in other sheet or workbook, you need to add that sheet or workbook first and assign it to another variable (for example ws3 or wsDiffs). That's all explained in my YouTube series to Compare Everything in Excel:
Part 1 (Workbooks): https://youtu.be/oTzY44uou1Q?si=K0U-1KGXo1qZCq6d
Part 2.1 (Worksheet properties): https://youtu.be/jyZHOIbHtv8?si=vTXRKEWfbsUi6-6v
Part 2.2 (Worksheet cell-to-cell): https://youtu.be/T4QMCoFlVdc?si=aCaZobp6TVST6iH5
Part 3 (Datasets): https://youtu.be/gXDBkT65KLg?si=GGzwN-EvOP_n45gR
In those videos I think I call the sheet "diffs" and add the differences there. If you want it in a separate workbook, just add this before you start the comparison loops:
Workbooks.Add
Set wbDiffs = ActiveWorkbook
Then whenever there is a difference, add that word or cell position to the other file with:
wbDiffs.ActiveSheet.Range("A" & lastRow+1).Value = cell.Value
Have a look also at my Add-in CompareXLS: https://excelmacrobusiness.blogspot.com/2023/01/excel-comparexls-add-in.html
This is exactly what I was looking for. Thank you very much. However, I want to have the additional option which you mentioned as:
ReplyDelete“Additionally, a file dialog could be used to select the second workbook, or even both workbooks to be compared (the macro would be then standing alone in a third workbook).”
Can you please provide me the updated macro with the above option. I’ll be very grateful.
Sorry for the addendum for clarification. I want the option where the macro is saved in a stand alone third workbook and dialog box is used to open and compare the two files. The highlighting part (for changes) may be done in the latest updated file. The dialog can specify to open either the new file or the old file first. Thank you very much for your help.
Delete