VBA & Macro Problems

How to Find Duplicate Values Across Excel Worksheets and List Sheet Names

John Wilson Oct 21, 2026 868 views

Question details

The user needs to identify duplicate values that appear on multiple worksheets within an Excel workbook and generate a consolidated list containing these values alongside the names of the worksheets they originate from.

How to Find Duplicate Values Across Excel Worksheets Using VBA

Product: Excel
Device & OS: not provided
Scenario: Consolidating data and tracking repetitive entries across various tabs in a large workbook to improve data accuracy.
Observed behavior: A VBA script must be constructed to extract values found on more than one sheet and output them into a newly created 'Duplicates' worksheet, mapped to their source sheet names.

Before you start

Ensure you have saved a backup of your workbook as an Excel Macro-Enabled Workbook (.xlsm) and that the Developer tab is enabled in your Excel ribbon so you can access the VBA Editor.

Solution 1Recommended

Use a VBA Macro with Scripting.Dictionary

This solution uses a VBA script featuring the Scripting.Dictionary object to map cell values to worksheets, allowing you to quickly isolate duplicates and output their locations.

Using a Scripting.Dictionary allows Excel to efficiently map each unique value to a concatenated string of worksheets where it appears. The script counts occurrences, skipping unique values, and isolates only the items found on more than one sheet.

Open the VBA Editor

Press 'Alt + F11' on your keyboard to launch the Visual Basic for Applications (VBA) editor.

Insert a New Module

Click 'Insert' from the top menu bar, then select 'Module' to open a blank script window.

Add the Dictionary Macro Code

Paste your VBA code. The code should instantiate the dictionary using 'CreateObject("Scripting.Dictionary")', loop through target worksheets ('For Each ws In ThisWorkbook.Worksheets'), and build a string appending 'ws.Name' for each matching cell value.

Create the Output Sheet

Ensure your script includes instructions to add a new worksheet named 'Duplicates' ('Worksheets.Add.Name = "Duplicates"'). Loop through the dictionary keys; if a key is associated with multiple sheets, write the key to Column A and the sheet names string to Column B.

Run the Macro

Press 'F5' or click the green 'Run' arrow in the toolbar. Return to your Excel workbook to review the newly generated 'Duplicates' worksheet.

Handling Blank Cells and Case Sensitivity: Add an 'If Len(Trim(cell.Value)) > 0' condition to prevent blank cells from being flagged as duplicates. If you want the search to be case-insensitive, use 'UCase(cell.Value)' when adding items to the dictionary.

Efficient Data Management with WPS Office

Find Duplicates Easily in WPS Spreadsheet

WPS Spreadsheet fully supports VBA macros, allowing you to run complex Scripting.Dictionary macros to find duplicates across worksheets. It also offers powerful built-in formatting tools for highlighting repetitive data without writing any code.

1. Enable Macros in WPS: Open your macro-enabled workbook in WPS Spreadsheet and navigate to the Developer tab to enable VBA execution.
2. Run VBA Code: Click 'VBA Editor', paste your Scripting.Dictionary macro code, and run it exactly as you would in Microsoft Excel.
3. Use Built-in Conditional Formatting: For a quick, non-macro approach on a single sheet, select your data range, click 'Home' > 'Conditional Formatting' > 'Highlight Cells Rules' > 'Duplicate Values'.

Fully compatible with Microsoft Excel .xlsm files and standard VBA macro scriptsBuilt-in 'Highlight Duplicates' feature for instant visual data analysisFree, lightweight, and easy to use on Windows, Mac, and LinuxTabbed interface makes comparing and navigating multiple worksheets seamless

microsoft office alternative - wps office

Frequently Asked Questions

Why do I get a 'User-defined type not defined' error when using Scripting.Dictionary?

This error occurs if you use early binding (e.g., 'Dim dict As Scripting.Dictionary') without enabling the required reference. To fix it, either go to Tools > References in the VBA editor and check 'Microsoft Scripting Runtime', or use late binding by declaring it as an Object and using 'CreateObject("Scripting.Dictionary")'.

Can I highlight the duplicate cells on their original worksheets using this VBA method?

Yes. Within the second loop of your code that identifies an item as a duplicate, you can traverse back through the worksheets and add a command like 'cell.Interior.Color = vbYellow' to highlight the matching cells.

Does this macro scan every cell on the worksheet?

By default, scanning every cell (over 17 billion per sheet) will freeze Excel. You must restrict the loop to a specific range, such as 'ws.UsedRange' or a specific column like 'ws.Range("A2:A" & lastRow)', to ensure the macro runs efficiently.

Can I find duplicates across multiple separate Excel workbooks?

The standard Scripting.Dictionary macro loops through 'ActiveWorkbook.Worksheets'. To find duplicates across different workbooks, you must modify the code to loop through a collection of open workbooks ('For Each wb In Application.Workbooks') or use Power Query to append the files.

Excel & Data Problems