VBA: How to efficiently filter and copy data from multiple sheets into a summary sheet without duplicates?
I'm performance testing and I'm building a feature where I'm trying to create a macro in Excel VBA that filters data from multiple worksheets and consolidates it into a summary sheet while ensuring there are no duplicate entries. The complication arises because the source sheets have different structures, and I want to copy only specific columns based on certain criteria. I initially attempted to use the `Union` method to combine ranges, but I keep running into issues with duplicate entries and performance. Here's a snippet of what I've tried so far: ```vba Sub ConsolidateData() Dim ws As Worksheet Dim summarySheet As Worksheet Dim lastRow As Long, summaryRow As Long Dim cell As Range Dim uniqueValues As Collection Set uniqueValues = New Collection Set summarySheet = ThisWorkbook.Sheets("Summary") summaryRow = 1 For Each ws In ThisWorkbook.Worksheets If ws.Name <> "Summary" Then lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row For Each cell In ws.Range("A2:A" & lastRow) On Error Resume Next ' Check unique values in column A uniqueValues.Add cell.Value, CStr(cell.Value) If Err.Number = 0 Then ' Copy specific columns to summary sheet summarySheet.Cells(summaryRow, 1).Value = cell.Value summarySheet.Cells(summaryRow, 2).Value = cell.Offset(0, 1).Value summaryRow = summaryRow + 1 End If On Error GoTo 0 Next cell End If Next ws End Sub ``` When I run this code, it works but is painfully slow with larger datasets. Additionally, I notice that sometimes the values are not being added to the `Collection`, resulting in duplicates in my summary sheet. I've tried using `Application.ScreenUpdating = False` to improve performance, but it still feels sluggish. Is there a more efficient way to filter and copy data from various sheets while ensuring that I avoid duplicates? Maybe a different method for handling the collections or optimizing the loop would help? Any advice would be appreciated! My development environment is macOS. How would you solve this? I'm developing on Linux with Vba. I'd love to hear your thoughts on this. My team is using Vba for this web app. I'm using Vba LTS in this project. Any examples would be super helpful.