Thursday, February 17, 2022

Compare Two Workbooks Excel VBA Macro

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.

Learn all about Microsoft Excel and VBA macros in Excel Macro Class with plenty of Excel macro examples and VBA learning materials. Find useful Excel macros to automate your daily tasks and work in Microsoft Excel.

Macro code:

 
  Sub CompareTwoWorkbooks()
 
  Dim wb1 As Workbook, wb2 As Workbook
  Dim ws1 As Worksheet, ws2 As Worksheet, wsMatch As Boolean
 
  'Open a file dialog to select workbooks (optional)
 
  'Set each of the 2 workbooks to object variables
  Set wb1 = ThisWorkbook
  Set wb2 = Workbooks(2)
 
  If wb1.Worksheets.Count = wb2.Worksheets.Count Then
 
    'Loop through worksheets
    For Each ws1 In wb1.Worksheets
      wsMatch = False
      For Each ws2 In wb2.Worksheets
        If ws1.Name = ws2.Name Then
          wsMatch = True
          'Compare the 2 worksheets
          For Each cell In ws1.Range("A1").CurrentRegion
            If cell.Value <> ws2.Range(cell.Address).Value Then
                cell.Interior.Color = vbYellow
                MsgBox "Mismatch cell " & cell.Address & " in worksheet " & ws1.Name
            End If
          Next cell
          Exit For
        End If
      Next ws2
      If wsMatch = False Then ws1.Tab.Color = vbYellow
    Next ws1
  Else
    MsgBox "Worksheets mismatch"
  End If
 
  End Sub
 

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:

 

at
Labels:

6 comments:

  1. DexterJune 17, 2023 at 10:44 AM

    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?

    ReplyDelete
    Replies
    1. Excel Macro ManiaJune 24, 2023 at 3:29 AM

      You 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

      Now, 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.

      Delete
  2. LegendBoiNovember 22, 2023 at 11:47 AM

    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.

    ReplyDelete
  3. Excel Macro ManiaNovember 26, 2023 at 12:39 AM

    You 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:
    For 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

    ReplyDelete
  4. LegendBoiNovember 29, 2023 at 1:04 PM

    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 ?
    Thanks in advance for the help

    ReplyDelete
  5. Excel Macro ManiaDecember 5, 2023 at 12:00 AM

    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:
    If 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

    ReplyDelete

Popular Posts