VBA Code to Compare Two Excel Sheets and Copy Differences

Microsoft Excel is a powerful software. We can perform numerous operations on our datasets using Excel tools and features. Sometimes, we have to make comparisons between multiple excel worksheets. The reason could be about finding the matches or copying the differences. There are some methods to carry out these operations. But applying the VBA code is the most effective and quick one to get the job done. In this article, we’ll show you 2 practical examples of VBA Code to Compare Two Excel Sheets and Copy Differences.


Download Practice Workbook

Download the following workbook to practice by yourself.


2 Examples of VBA Code to Compare Two Excel Sheets and Copy Differences

To illustrate, we’ll use a sample dataset as an example. For instance, the following dataset represents the Salesman, Product, and Net Sales. It’s in the Sheet1. But, here you can see that the 7th and 8th rows are empty.

Again, in Sheet2 of the same excel file, the complete dataset is there with both the 7th and 8th rows. This article will show you how to compare two excel sheets present in the same file and also in different files. Then, extract the differences into a new sheet.


1. Apply VBA Code to Compare Two Excel Sheets of Same File and Copy Differences

In our first example, you’ll see the process to apply VBA Code to Compare Two Excel Sheets of the Same File and Copy Differences. Therefore, follow the steps below carefully to perform the task.

STEPS:

  • First, go to Developer Visual Basic.

Apply VBA Code to Compare Two Excel Sheets of Same File and Copy Differences

  • As a result, the VBA window will pop out.
  • Then, click Insert Module.

Apply VBA Code to Compare Two Excel Sheets of Same File and Copy Differences

  • Consequently, the Module window will appear.
  • Copy the following code and paste it into the box.
Option Explicit
Sub Compare_Two_Excel_Sheets()
    'Define Fields
    Dim iR As Double, iC As Double, oRw As Double
    Dim iRow_M As Double, iCol_M As Double
    Dim s1 As Worksheet, s2 As Worksheet
    Dim s3 As Worksheet
    Set s1 = ThisWorkbook.Sheets(1)
    Set s2 = ThisWorkbook.Sheets(2)
    Set s3 = ThisWorkbook.Sheets(3)
    iRow_M = s1.UsedRange.Rows.Count
    iCol_M = s1.UsedRange.Columns.Count
    For iR = 1 To iRow_M
    For iC = 1 To iCol_M
        s1.Cells(iR, iC).Interior.Color = xlNone
        s2.Cells(iR, iC).Interior.Color = xlNone
        If s1.Cells(iR, iC) <> s2.Cells(iR, iC) Then
           s1.Cells(iR, iC).Interior.Color = vbYellow
           s2.Cells(iR, iC).Interior.Color = vbYellow
           oRw = oRw + 1
           s3.Cells(oRw, 1) = s1.Cells(iR, iC)
           s3.Cells(oRw, 2) = s2.Cells(iR, iC)
        End If
    Next iC
    Next iR
End Sub

Apply VBA Code to Compare Two Excel Sheets of Same File and Copy Differences

  • After that, save the file and press the F5 key to run the code.
  • Hence, it’ll return the differences in a new worksheet (Sheet3).

Apply VBA Code to Compare Two Excel Sheets of Same File and Copy Differences

  • Moreover, you’ll see the differences get highlighted in the sheets.
  • In the below figure, the 7th and 8th rows are in Yellow in Sheet1.

Apply VBA Code to Compare Two Excel Sheets of Same File and Copy Differences

  • Likewise, Sheet2 gets highlighted.
  • In this way, you can apply the VBA code to compare excel sheets.

Apply VBA Code to Compare Two Excel Sheets of Same File and Copy Differences

Read More: Macro to Copy and Paste from One Worksheet to Another (15 Methods)


2. Compare Two Sheets of Different Excel Files and Copy Differences with VBA

We’ll also cover the case where our Excel sheets are in different Excel files. The below dataset is present in Sheet1 of the File1 excel file.

Compare Two Sheets of Different Excel Files and Copy Differences with VBA

Similarly, the completed version of the same dataset is present in Sheet1 of the File2 excel file. Look at the following picture to understand the case.

Compare Two Sheets of Different Excel Files and Copy Differences with VBA

Now, learn the process to compare the sheets and copy the differences.

STEPS:

  • Firstly, open a new Excel file.
  • Then, go to the Developer tab as we showed above.
  • Select Visual Basic.
  • Afterward, click Module from the Insert drop-down.
  • As a result, the Module dialog box will emerge.
  • Subsequently, copy the below code and paste it there.
Sub Compare_Two_Files()
SummaryFile = "vba code"
SummarySheet = "Sheet1"
File1 = "D:\SOFTEKO\vba code to compare two excel sheets and copy differences\File1.xlsx"
File2 = "D:\SOFTEKO\vba code to compare two excel sheets and copy differences\File2.xlsx"
File1_Sheet = "Sheet1"
File2_Sheet = "Sheet1"
Set Workbook1 = Workbooks.Open(File1)
Set Workbook2 = Workbooks.Open(File2)
Set Rng1 = Workbook1.Worksheets(File1_Sheet).UsedRange
Set Rng2 = Workbook2.Worksheets(File2_Sheet).UsedRange
Count = 1
For i = 1 To Rng1.Rows.Count
For j = 1 To Rng1.Columns.Count
If Rng1.Cells(i, j) <> Rng2.Cells(i, j) Then
    For k = 1 To Rng1.Rows.Count    Workbooks(SummaryFile).Worksheets(SummarySheet).Cells(Count, k) = Rng2.Cells(i, k)
    Next k
    Count = Count + 1
    Exit For
End If
Next j
Next i
End Sub

  • Next, save the file.
  • Run the code by pressing F5.
  • Thus, you’ll get the differences in a sheet of the new excel file.
  • In such a way, you can compare the excel sheets and copy differences.

Read More: Excel VBA to Copy Rows to Another Worksheet Based on Criteria


Conclusion

Henceforth, you will be able to apply the VBA Code to Compare Two Excel Sheets and Copy Differences following the above-described procedures. Keep using them and let us know if you have more ways to do the task. Follow the ExcelDemy website for more articles like this. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.


Related Articles

Aung

Aung

I'm Aung. Recently I earned my B.Sc. Degree in Electrical and Electronic Engineering. From now on, I will be working in Microsoft Excel and other useful software, and I’ll upload articles related to them. My current goal is to write technical contents for anybody and everybody that will make the learning process of new software and features a happy journey.

We will be happy to hear your thoughts

Leave a reply

ExcelDemy
Logo