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.
- First, go to Developer ➤ Visual Basic.
- As a result, the VBA window will pop out.
- Then, click Insert ➤ Module.
- 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
- 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).
- 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.
- Likewise, Sheet2 gets highlighted.
- In this way, you can apply the VBA code to compare excel sheets.
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.
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.
Now, learn the process to compare the sheets and copy the differences.
- 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.
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.
- Excel VBA: Copy Cell Value and Paste to Another Cell
- Copy Multiple Cells to Another Sheet in Excel (9 Methods)
- How to Copy Rows Automatically in Excel to Another Sheet (4 Methods)
- Use VBA to Paste Values Only with No Formatting in Excel
- Macro to Copy Specific Columns from One Worksheet to Another in Excel
- How to Copy Unique Values to Another Worksheet in Excel (5 Methods)
- Copy and Paste Thousands of Rows in Excel (3 Ways)