Hi John,

Give this a try

Dim uniq As New Collection
Dim sh As Worksheet
Dim oWb As Workbook
Dim iTarget As Long

Set oWb = Workbooks.Add
For Each sh In ActiveWorkbook
For Each ce In Range("J2", Range("J" & Rows.Count).End(xlUp))
On Error Resume Next
uniq.Add Item:=ce.Value, key:=CStr(ce.Value)
Next ce
For Each ce In uniq
oWb.Worksheets(1).Range("K1").Offset(iTarget, 0).Value = ce
iTarget = iTarget + 1
Next ce
Next sh

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

"John Phinney" wrote in message
...
I need some assistance and didn't know where else to turn. I have this
code that I got I think from google but it was some time ago. I have used
it many different times and it works great. I know how to edit most of it
to fit my needs, but I have come across a new challenge that I think this
will work for, but I am not sure how to set it up. Here is the code I have.

Dim uniq As New Collection
For Each ce In Range("J2", Range("J65536").End(xlUp))
On Error Resume Next
uniq.Add Item:=ce.Value, key:=CStr(ce.Value)
Next ce
Range("K1").Select
For Each ce In uniq
ActiveCell.Value = ce
ActiveCell.Offset(1, 0).Select
Next ce

This works on whatever the active sheet is. It finds all the unique
entries in column J and puts them into column K starting in row one on the
same page. What I need is a way to have it look in column J on ALL
WORKSHEETS in a workbook (not knowing how many there will be each time),
find the unique entries, and put them on a worksheet in A DIFFERENT
WORKBOOK.

I think to get them into another workbook, I just need to put in to
activate a new workbook and worksheet right before the line:
Range("K1").Select, that much I know how to do. I know excel and VBA
fairly well, but as to how to set the range to look at all worksheets, I am
lost.

Any help would be greatly appreciated, I think this should be able to
work, I just don't know how to do it, and I can't find anything in google
that seems to make sense, at least to me.

Thanks,

John Phinney