I have and Excel document with several individual worksheets. On a seperate
sheet I would like to create a list of each name that appears on the
spreadsheets. Some of the names appear several times but I do not want any
name listed more than once on the new sheet. I would like each name to have
it's own cell and don't want to have to go back through the sheets to capture
every single name. Any ideas how this can be done? Any help would be
appreciated.

One way:

Check out

http://www.contextures.com/xladvfilter01.html#ExtractWs

Extract unique values to your summary sheet, then filter unique values
if there are duplicates between sheets.


In article ,
The BriGuy wrote:

I have and Excel document with several individual worksheets. On a seperate
sheet I would like to create a list of each name that appears on the
spreadsheets. Some of the names appear several times but I do not want any
name listed more than once on the new sheet. I would like each name to have
it's own cell and don't want to have to go back through the sheets to capture
every single name. Any ideas how this can be done? Any help would be
appreciated.

Here is some code to do it. Select the column with the names in it and run
the macro. It will create a new sheet with the unique names in it...

Public Sub GetUniqueItems()
Dim cell As Range 'Current cell in range to check
Dim rngToSearch As Range 'Cells to be searched
Dim dic As Object 'Dictionary Object
Dim dicItem As Variant 'Items within dictionary object
Dim wks As Worksheet 'Worksheet to populate with
unique items
Dim rngPaste As Range 'Cells where unique items are
placed

Application.ScreenUpdating = False
'Create range to be searched
Set rngToSearch = Intersect(ActiveSheet.UsedRange, Selection)
If rngToSearch Is Nothing Then Set rngToSearch = ActiveCell

'Confirm there is a relevant range selected
If Not rngToSearch Is Nothing Then
'Create dictionay object
Set dic = CreateObject("Scripting.Dictionary")

'Populate dictionary object with unique items (use key to define
unique)
For Each cell In rngToSearch 'Traverse selected range
If Not dic.Exists(cell.Value) And cell.Value < Empty Then
'Check the key
dic.Add cell.Value, cell.Value 'Add the item if unique
End If
Next

If Not dic Is Nothing Then 'Check for dictionary
Set wks = Worksheets.Add 'Create worksheet to populate
Set rngPaste = wks.Range("A1") 'Create range to populate
For Each dicItem In dic.Items 'Loop through dictionary
rngPaste.NumberFormat = "@" 'Format cell as text
rngPaste.Value = dicItem 'Add items to new sheet
Set rngPaste = rngPaste.Offset(1, 0) 'Increment paste range
Next dicItem
'Clean up objects
Set wks = Nothing
Set rngPaste = Nothing
Set dic = Nothing
End If
End If
Application.ScreenUpdating = True
End Sub
--
HTH...

Jim Thomlinson


"The BriGuy" wrote:

I have and Excel document with several individual worksheets. On a seperate
sheet I would like to create a list of each name that appears on the
spreadsheets. Some of the names appear several times but I do not want any
name listed more than once on the new sheet. I would like each name to have
it's own cell and don't want to have to go back through the sheets to capture
every single name. Any ideas how this can be done? Any help would be
appreciated.