Copy Column A from All Excel Worksheets and Remove Duplicates Using VBA
Question details
The user needs to aggregate data from Column A of every worksheet into a single new sheet and remove duplicate names using exact value comparison.
- Product
- Excel
- Device & OS
- not provided
- Scenario
- Consolidating lists of names or data from multiple sheets into one master list without repeating identical entries.
- Observed behavior
- Requires exact-value duplicate removal to ensure names that contain other names (like Beth and Elizabeth) are recognized as completely distinct entries rather than duplicates.
Ensure your workbook is saved as a Macro-Enabled Workbook (.xlsm) and that you have enabled macros in your Trust Center or security settings.
Use a VBA Macro to Consolidate Data and Remove Duplicates
Create a macro that systematically loops through all worksheets, copies the used range of Column A into a new sheet, and applies the built-in RemoveDuplicates method.
Using VBA is the most efficient method for automating this task across a large number of sheets. The built-in Range.RemoveDuplicates method uses exact string matching natively, so you do not have to worry about substring errors (e.g., matching 'Beth' with 'Elizabeth').
Press ALT + F11 to launch the Visual Basic for Applications (VBA) editor in your spreadsheet application.
Click 'Insert' from the top menu and select 'Module' to create a blank script window.
Write a script that creates a new destination worksheet. Use a 'For Each' loop to iterate through all sheets. In the loop, find the last used row in Column A of the current sheet and copy it to the next available empty row in your destination sheet.
At the end of your macro, apply the duplicate removal to the consolidated column using the exact syntax: Range("A:A").RemoveDuplicates Columns:=1, Header:=xlNo (or xlYes if you have a header row).
Press F5 or click the Run button to execute the macro. Check the new worksheet for your clean, consolidated list.
Use Power Query to Append Data (Excel 365 Alternative)
If you prefer a no-code solution and are using modern versions of Excel, Power Query can append data from all sheets and remove duplicates.
Consolidate Data and Run Macros with WPS Spreadsheet
WPS Office provides robust support for VBA macros and advanced data tools, allowing you to easily automate data consolidation tasks across multiple worksheets just like in Microsoft Excel.
- 1. Enable Macros in WPS: Open your workbook in WPS Spreadsheet. Go to the 'Tools' tab and ensure Macro functionality is enabled.
- 2. Open the Developer Tools: Click on 'Macros' or press ALT + F11 to launch the built-in VBA Editor.
- 3. Run Your Consolidation Code: Paste your looping and duplicate-removal macro into a Module, then execute it to instantly aggregate Column A across all sheets.
Frequently Asked Questions
Why is my macro incorrectly flagging 'Beth' and 'Elizabeth' as duplicates?
This happens if your macro uses a custom loop with the 'InStr' function or wildcard matching, which detects 'Beth' inside 'Elizabeth'. To fix this, use the built-in Range.RemoveDuplicates method, which enforces exact-value matching by default.
How do I dynamically find the last used row in Column A for each worksheet via VBA?
In your VBA loop, use the code `LastRow = Ws.Cells(Ws.Rows.Count, "A").End(xlUp).Row`. This correctly identifies the last populated cell in Column A, ensuring you don't copy thousands of blank rows into your master sheet.
Does WPS Spreadsheet support running Excel VBA macros for duplicate removal?
Yes, WPS Office Pro and certain customized editions fully support VBA. You can open the VBA editor (ALT + F11), paste standard Excel VBA scripts, and execute them perfectly, including the Range.RemoveDuplicates function.
Can I specify whether my column has a header when removing duplicates?
Yes. When calling the RemoveDuplicates method in VBA, append the parameter `Header:=xlYes` if your data includes headers, or `Header:=xlNo` if it does not, so that your title row is not accidentally sorted or removed.
