VBA to Consolidate Data from Multiple Sheets Based on Dynamic Criteria - Unexpected Output
I'm updating my dependencies and I'm trying to implement I'm upgrading from an older version and I'm confused about I'm experimenting with I'm trying to consolidate data from multiple sheets into a summary sheet using VBA..... My goal is to pull data from sheets named "Data1", "Data2", etc., based on a dynamic criteria that changes based on user input. However, I'm encountering unexpected output where the summary sheet shows duplicate entries instead of unique ones. I've implemented a loop that checks each sheet, but it seems to be appending data incorrectly. Here's the code I'm currently using: ```vba Sub ConsolidateData() Dim ws As Worksheet Dim summaryWs As Worksheet Dim lastRow As Long Dim summaryRow As Long Dim criteria As String Dim cell As Range ' Set the criteria for filtering data criteria = InputBox("Enter criteria to filter data:") Set summaryWs = ThisWorkbook.Sheets("Summary") summaryRow = 1 ' Start writing in the first row of Summary sheet ' Loop through each sheet in the workbook For Each ws In ThisWorkbook.Sheets If ws.Name <> "Summary" Then lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row ' Check each entry in the current sheet For Each cell In ws.Range("A1:A" & lastRow) If cell.Value = criteria Then ' Append matching row to the summary sheet cell.EntireRow.Copy Destination:=summaryWs.Cells(summaryRow, 1) summaryRow = summaryRow + 1 End If Next cell End If Next ws End Sub ``` When I run this code, I expect to get a consolidated list of unique rows based on the criteria provided. However, the summary sheet ends up including duplicate rows if the same criteria match across different sheets. I've tried adding a check to see if the entry already exists in the summary sheet, but that leads to performance issues because it has to loop through the entire summary for each match. Is there a more efficient way to achieve this? Is there a specific method or approach that can help ensure unique entries without significantly degrading performance? I'm using Excel 2016 and the workbook contains a considerable amount of data, which may be impacting the performance. Any guidance would be greatly appreciated! The stack includes Vba and several other technologies. Cheers for any assistance! I'm using Vba 3.10 in this project. What would be the recommended way to handle this? My team is using Vba for this microservice. I'm open to any suggestions. I'm on Windows 11 using the latest version of Vba. What's the correct way to implement this? My team is using Vba for this microservice. Is this even possible?