Arlen,

Not sure why the first group would occupy 8 rows, and the next group 17 rows. This code will copy
the unique number values from the activesheet's column C to a new workbook, with 6 sets of the
numbers in column A. I' ve also assumed that the numbers are constants, not the returned value from
functions.

Sub TryNow()
Dim myB As Workbook
Dim mySh As Worksheet
Dim i As Integer
Dim myCopies As Integer
myCopies = 6

Set mySh = ActiveSheet
With Range("C:C")
.AdvancedFilter Action:=xlFilterInPlace, Unique:=True
.SpecialCells(xlCellTypeVisible).SpecialCells(xlCe llTypeConstants, 1).Copy
Set myB = Workbooks.Add
myB.Sheets(1).Cells(1, 1).PasteSpecial
For i = 2 To myCopies
myB.Sheets(1).Cells(Rows.Count, 1).End(xlUp)(2).PasteSpecial
Next i
ThisWorkbook.Activate
mySh.ShowAllData
End With

End Sub

HTH,
Bernie
MS Excel MVP


"Arlen" wrote in message
...
I have quite literally hundreds of worksheets where I need to filter out the
unique numbers in Column C, and paste them into a single new filtered list in
a new workbook.

I recorded a Macro and it looks like this:

Columns("C:C").Select
ActiveSheet.ListObjects.Add(xlSrcRange, Range("$C:$C"), , xlYes).Name =
"List1"
Columns("C:C").Select
Columns("C:C").AdvancedFilter Action:=xlFilterInPlace, Unique:=True
Selection.Copy
With ActiveWindow
.Top = 1.75
.Left = -389
End With
Windows("Book3").Activate
ActiveSheet.Paste
End Sub


This needs a few tweaks, but I don't know VBA.
How do I copy only the numbers once the filter has been applied. So
something like Selection.Copy(NumbersOnlyPlease) ?
Then, how do I paste those numbers consecutively down Column 1 in Book3 so
that the first group occupies, say A1-A8, then the next occupies A9-A25,
perhaps.

Thanks for your help.

Arlen