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.
VBA Code to Compare Two Excel Sheets and Copy Differences: 2 Examples
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 for different values 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 of applying 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.
- 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 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.
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: Compare Two Excel Sheets and Highlight Differences Macro
Download Practice Workbook
Download the following workbook to practice by yourself.
<< Go Back to Learn Excel | Compare | Worksheets
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. Don’t forget to drop comments, suggestions, or queries if you have any in the comment section below.
Hello Aung. Nice work. I have an issue I need assistance on. I have a workbook with userform. I want to make different users access and edit the file on a network computer. How can I do that? Is it possible to keep the main workbook and have just a shell userform pulling data from the network workbook and displaying it and all changes made made can then be saved even though the workbook is closed?
Hi NOUR,
Thanks for the appreciation.
As per your requirement, it is possible to allow multiple users to access and edit a workbook on a network computer. Here are some steps you can follow in this regard:
1. Save your workbook on a shared network drive that all users have access to.
2. Restrict editing access for other users to ensure only authorized users can edit the workbook.
3. Create a login form that allows users to enter their credentials to access the userform. You can store user credentials in a separate worksheet within the workbook or use an external database.
4. Use VBA code to pull the necessary data from the shared workbook and display it to the userform.
5. When a user makes changes to the data in the userform, use VBA code to write the changes back to the shared workbook.
6. Implement a save feature that automatically saves changes made to the shared workbook when the userform is closed.
7. Create a backup plan to ensure data is not lost in case of system failure or network issues. This can include regular backups or saving a copy of the workbook in a secure location.
8. Test the userform with different user accounts to ensure it works as intended and all users can access and edit the shared workbook.
Hope this guide helps you to approach and solve the issue you’re facing. Actually it is not possible to give an exact solution without checking the Excel file. Most probably you need physical assistance in this regard. Let us know if you have any additional questions or concerns. You can also send your Excel file to our official mail address: [email protected]
Have a great day!
Regards
Rafiul Hasan | ExcelDemy Team