How To Find Duplicate Values In Different Excel Sheets Vba

How to Find Duplicate Values in Different Excel Sheets using VBA

To find duplicate values in different Excel sheets using VBA, you can utilize the power of macros and loops. Here’s a detailed explanation with examples:

Step 1: Set Up the Excel Workbook

Start by creating an Excel workbook with multiple sheets containing the data. For this example, let’s assume you have two sheets named “Sheet1” and “Sheet2”.

Step 2: Open the VBA Editor

Press “Alt + F11” to open the VBA editor within Excel.

Step 3: Insert a New Module

Right-click on your workbook name in the Project Explorer pane and select “Insert” > “Module”.

Step 4: Write the VBA Code

Paste the following VBA code into the newly inserted module:

Sub FindDuplicateValues() Dim ws1 As Worksheet, ws2 As Worksheet Dim cell1 As Range, cell2 As Range


        ' Set the worksheets to compare

        Set ws1 = ThisWorkbook.Sheets("Sheet1")

        Set ws2 = ThisWorkbook.Sheets("Sheet2")
        ' Loop through all cells in Sheet1

        For Each cell1 In ws1.UsedRange

            ' Loop through all cells in Sheet2

            For Each cell2 In ws2.UsedRange

                ' Check if cell values match

                If cell1.Value = cell2.Value Then

                    ' Highlight the duplicate cell in Sheet1

                    cell1.Interior.Color = RGB(255, 0, 0)

' Highlight the duplicate cell in Sheet2 cell2.Interior.Color = RGB(255, 0, 0) End If Next cell2 Next cell1 End Sub

Step 5: Run the Macro

To run the macro and find duplicate values, press “F5” or click the “Run” button in the VBA editor. The macro will compare all cells in Sheet1 with all cells in Sheet2. If any duplicate values are found, the corresponding cells will be highlighted in red.

Step 6: Review the Results

After running the macro, review the highlighted cells in both sheets to identify the duplicate values. You can then take appropriate actions based on your requirements.

Additional Tips

You can modify the code to compare more than two sheets by adding additional worksheet variables and looping through them.
Instead of highlighting the duplicate cells, you can perform other actions such as copying them to another location or removing them from the sheets.
Make sure the data in your workbook is organized consistently for accurate results.

By following these steps and understanding the provided VBA code, you should be able to find duplicate values in different Excel sheets using VBA.