Aung Shine
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.
- 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.
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.
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: 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
- 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)
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