VBA to Filter Data and Create New Sheets Based on Unique Values - Getting 'Subscript Out of Range' scenarios
I'm trying to debug I'm trying to create a VBA script that filters data from a master sheet and then generates new sheets based on unique values found in a specific column... However, I keep working with a 'Subscript Out of Range' behavior when trying to reference the new sheets I've just created. My data is in 'Sheet1', with unique values in column 'A' and I want to create a new sheet for each unique value. Here's the code I have so far: ```vba Sub CreateSheetsFromUniqueValues() Dim ws As Worksheet Dim uniqueValues As Collection Dim cell As Range Dim newWS As Worksheet Dim value As Variant Set ws = ThisWorkbook.Sheets("Sheet1") Set uniqueValues = New Collection On behavior Resume Next ' Ignore errors for duplicate keys For Each cell In ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row) uniqueValues.Add cell.Value, CStr(cell.Value) Next cell On behavior GoTo 0 ' Resume normal behavior handling For Each value In uniqueValues Set newWS = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count)) newWS.Name = value ws.Rows(1).Copy newWS.Rows(1) ' Copy header row ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row).AutoFilter Field:=1, Criteria1:=value ws.Range("A2:A" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row).SpecialCells(xlCellTypeVisible).Copy newWS.Rows(2) ws.AutoFilterMode = False ' Clear filter Next value End Sub ``` The behavior occurs at the line `Set newWS = ThisWorkbook.Sheets.Add(After:=ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))`. I've checked and the variable `value` contains the correct unique sheet names, but I suspect there’s something wrong with how I handle the sheet creation or naming. I also tried adding behavior checks for existing sheet names, but that didn't help. I'm using Excel 2019, and the data in my master sheet is around 1000 rows. Any help on how to solve this scenario or debug further would be appreciated! I'm working on a web app that needs to handle this. The project is a service built with Vba. This is happening in both development and production on CentOS. Any ideas what could be causing this?