How to Extract Unique Values from a Range and Write Them to a New Worksheet Using VBA?
I tried several approaches but none seem to work. I'm trying to extract unique values from a specific range in my Excel worksheet and write them to a new worksheet using VBA. My goal is to take a list of names in column A of 'Sheet1' and create a new sheet called 'UniqueNames' that contains only the unique names without any duplicates. I’ve written the following code, but I keep encountering a 'Run-time error 9: Subscript out of range' when trying to reference the new worksheet. Here's what I have so far: ```vba Sub ExtractUniqueValues() Dim wsSource As Worksheet Dim wsDest As Worksheet Dim uniqueNames As Collection Dim cell As Range Dim name As Variant Dim lastRow As Long Set wsSource = ThisWorkbook.Sheets("Sheet1") lastRow = wsSource.Cells(wsSource.Rows.Count, "A").End(xlUp).Row ' Create a new worksheet for unique names On Error Resume Next Set wsDest = ThisWorkbook.Sheets("UniqueNames") On Error GoTo 0 If wsDest Is Nothing Then Set wsDest = ThisWorkbook.Sheets.Add wsDest.Name = "UniqueNames" End If ' Initialize collection to store unique names Set uniqueNames = New Collection ' Loop through each name in the source range For Each cell In wsSource.Range("A1:A" & lastRow) On Error Resume Next uniqueNames.Add cell.Value, CStr(cell.Value) ' Using the value as key to enforce uniqueness On Error GoTo 0 Next cell ' Write unique names to the new worksheet For name In uniqueNames wsDest.Cells(wsDest.Rows.Count, 1).End(xlUp).Offset(1, 0).Value = name Next name End Sub ``` When I run the macro, I'm not sure if the error is due to how I'm trying to create or reference the new worksheet. Is there a better way to check if the sheet exists without throwing an error? Also, could the way I'm using the `Collection` affect performance when dealing with a large number of names? Any advice on optimizing this code would be greatly appreciated!