How to Find Duplicate Values in Different Excel Sheets Using VBA

Finding duplicate values in different Excel sheets can be a common task, especially when dealing with large datasets. This can help you clean up data and ensure that your records are unique and accurate.

If you’re comfortable with using VBA (Visual Basic for Applications), you can automate this process to save time and prevent human error. In this tutorial, we’ll walk through using VBA to find duplicate values across different sheets within an Excel workbook.





To get started, we need to outline the steps required to find duplicate values using VBA:

  1. Open the Excel workbook with the sheets you want to compare.
  2. Define the range of cells you wish to check for duplicates.
  3. Write the VBA function to compare values across the sheets.
  4. Execute the VBA script to find and highlight the duplicates.

1. Writing the VBA Function

The VBA function will loop through the defined range of cells on one sheet, and for each cell, it will compare the value against all cells in a defined range on another sheet. If a duplicate is found, it could, for example, highlight both cells with a color for easy identification.

Here’s an example of what your VBA function may look like:

Remember to customize the Worksheet names (“Sheet1”, “Sheet2”), and the Define ranges (“A1:A10”) to suit your needs.

2. Executing the VBA Script

Once you’ve written your VBA script, you’ll need to run it within the Excel VBA Editor. After running the script, any duplicate values found according to your defined parameters will be highlighted in both sheets.

Ensure you save your work before running the script to prevent data loss.

3. Customizing the VBA Script

You can expand the functionality of this script depending on your needs. For example, instead of highlighting duplicates, you might want to list them on a new sheet, delete them, or perform some other action.





In this tutorial, we’ve learned how to create a VBA script to find duplicate values across different sheets in an Excel workbook. By customizing and running such scripts, you can significantly speed up the data-cleaning process and maintain the quality of your datasets. Remember to tailor the VBA code to fit the specifics of your workbook’s structure and your objectives.

Tomasz Decker is an Excel specialist, skilled in data analysis and financial modeling.

Posted in vba