this code put all the usique values into sheet1 of the workbook where
the macro is located. change the Variable Folder as required.

Sub GetUnique()

NewRow = 1
Set RsltSht = ThisWorkbook.Sheets("Sheet1")

Folder = "c:\temp\"
FName = Dir(Folder & "*.xls")
Do While FName < ""
Set bk = Workbooks.Open(Filename:=Folder & FName)
For Each sht In bk.Worksheets
With sht
LastRow = .Range("A" & Rows.Count).End(xlUp).Row
For RowCount = 1 To LastRow
Data = .Range("A" & RowCount)
If Data < "" Then
'lookup if data already exists
Set c = RsltSht.Columns("A").Find(what:=Data, _
LookIn:=xlValues, lookat:=xlWhole)
If c Is Nothing Then
'data didn't already exist
RsltSht.Range("A" & NewRow) = Data
NewRow = NewRow + 1
End If
End If
Next RowCount
End With
Next sht
bk.Close savechanges:=False
FName = Dir()
Loop




End Sub


--
joel
------------------------------------------------------------------------
joel's Profile: http://www.thecodecage.com/forumz/member.php?userid=229
View this thread: http://www.thecodecage.com/forumz/sh...d.php?t=151944

Microsoft Office Help