VBA - Struggling to work out why my 'collection' isn't populating

[tbablue]

Hi Forum,

My VBA is attempting to take copies of a table 'By_Reporting_Period', filtering on values in a field [Reporting_Period_Date], put that in a new new worksheet, before doing the same with the next filtered value in the same tablefield 'By_Reporting_Period[Reporting_Period_Date]'; essentially replicating the table on numerous sheets with every table broken out by the filtered values in [Reporting_Period_Date].

I'm struggling to work out why my collection, 'uniqueValues', remains empty and therefore errors on line 'For Each cell In uniqueValues'.
The data being passed is dates - every field is uniform - there are no blanks.

Any ideas? My code is shown in its entirety below.

......


Sub CopyTableByUniqueDates()

Dim wsSource As Worksheet
Dim tbl As ListObject
Dim keyColumn As Range
Dim cell As Range
Dim uniqueValues As Collection
Dim newWs As Worksheet
Dim filterFieldIndex As Integer

Set wsSource = ThisWorkbook.Sheets("Parameter_Sheet")
Set tbl = wsSource.ListObjects("By_Reporting_Period")
Set keyColumn = tbl.ListColumns("Reporting_Period_Date").DataBodyRange
filterFieldIndex = tbl.ListColumns("Reporting_Period_Date").Index
Set uniqueValues = New Collection

' Collect unique values
On Error Resume Next
For Each cell In keyColumn
uniqueValues.Add cell.Value, CStr(cell.Value)
Next cell
On Error GoTo 0

' Filter and copy data for each unique value

For Each cell In uniqueValues
Set newWs = Sheets.Add(After:=Sheets(Sheets.Count))
newWs.Name = Format(cell, "yyyy-mm-dd")

tbl.Range.AutoFilter Field:=filterFieldIndex, Criteria1:=cell
tbl.Range.SpecialCells(xlCellTypeVisible).Copy Destination:=newWs.Range("A1")
tbl.Range.AutoFilter Field:=filterFieldIndex
Next cell
On Error GoTo 0

End Sub

 

[RoryA]

Your collection doesn't contain ranges, it contains values.

 

[tbablue]

TY.

You made me realise that there was a better way to do this. I really appreciate you taking the time to respond. Most grateful.

I've marked your reply as a solution. Thanks gain.