Copy sets of 200 records for each unique item in an autofilter to new worksheets

[cmadsen]

Hello all,
Just cutting my teeth on VBA and have a question. I have a spreadsheet that I am filtering on a specific column. I have been able to create new worksheets containing the records matching each unique value in the autofilter. What I really want to do is create worksheets with groups of 200 records within each unique value. The unique values that do not have 200 records should just have the numbers of records in their set.

Here is what I have so far. It works great to create the separate sheets for each unique value, but I want to have them broken out in groups of 200:

'Create new sheets based on filter of Specialty Code column
Dim c As Range
Dim rng As Range
Dim LR As Long

'Last Row
LR = Cells(Rows.Count, "A").End(xlUp).Row
'Range of Data
Set rng = Range("A1:N" & LR)

'populate trailing column with unique values in column K. Used to loop through and create worksheets below
Range("K2:K" & LR).AdvancedFilter Action:=xlFilterCopy, CopyToRange:=Range("O1"), Unique:=True

For Each c In Range([O2], Cells(Rows.Count, "O").End(xlUp))
With rng
.AutoFilter
.AutoFilter Field:=11, Criteria1:=c.Value
.SpecialCells(xlCellTypeVisible).Copy
Sheets.Add(After:=Sheets(Sheets.Count)).Name = c.Value
ActiveSheet.Paste
Cells.Select
Cells.EntireColumn.autofit


End With
Next c

Appreciate the help!!!

Chad

 

[cmadsen]

Anybody have thoughts on this one??