Ok you have the combobox already so you can read the selected value

See this example that copy in a sheet named "Sheet2"
(you see it two times in the code)

And use a combobox on Sheet3 named ComboBox1
Sheets("Sheet3").ComboBox1.Value

Test this and you can add the code in the filesystemobject example on my site
http://www.rondebruin.nl/fso.htm


Sub Example13()
'Note: This example use the function LastRow
Dim basebook As Workbook
Dim mybook As Workbook
Dim rng As Range
Dim rnum As Long
Dim FNames As String
Dim MyPath As String
Dim SaveDriveDir As String
Dim str As String

SaveDriveDir = CurDir
MyPath = "C:\Data" '<<< Change
ChDrive MyPath
ChDir MyPath

FNames = Dir("*.xls")
If Len(FNames) = 0 Then
MsgBox "No files in the Directory"
ChDrive SaveDriveDir
ChDir SaveDriveDir
Exit Sub
End If

Application.ScreenUpdating = False
Set basebook = ThisWorkbook
str = Sheets("Sheet3").ComboBox1.Value

Do While FNames < ""

'Find the last row on the first sheet (used to copy the data below the data that exist)
rnum = LastRow(basebook.Worksheets("Sheet2")) + 1

'Open the workbook
Set mybook = Workbooks.Open(FNames)

With mybook.Sheets(1)
Set rng = Nothing

'Close AutoFilter first
.AutoFilterMode = False

'This example filter on column A for ron, Note: A1 is the Header cell
'Change the range and criteria to your Range/Criteria
.Range("A1:A100").AutoFilter Field:=1, Criteria1:=str

With .AutoFilter.Range

' Set a range without the Header cell
On Error Resume Next
Set rng = .Offset(1, 0).Resize(.Rows.Count - 1, 1) _
.SpecialCells(xlCellTypeVisible)
On Error GoTo 0

'If there is data copy the rows
If Not rng Is Nothing Then
rng.EntireRow.Copy basebook.Worksheets("Sheet2").Cells(rnum, "A")
End If

End With

'Close AutoFilter
.AutoFilterMode = False

End With

'Close the workbook
mybook.Close False

FNames = Dir()
Loop

ChDrive SaveDriveDir
ChDir SaveDriveDir
Application.ScreenUpdating = True
End Sub

--
Regards Ron de Bruin
http://www.rondebruin.nl


"deejayh" wrote in message
...

Hi Ron,

Do you want to use a combobox or a validation dropdown?

Whichever, see below
Do you know the values that must be in the combo?

I have used previously a combobox, with a list in the same workbook but
on another sheet, with 2 columns: Region, District
ie.. West, 101
The last figure"District" being the value required.
Also how can I set the data to go into a specific sheet?

Many thanks Ron,
Dave


--
deejayh
------------------------------------------------------------------------
deejayh's Profile: http://www.excelforum.com/member.php...o&userid=34110
View this thread: http://www.excelforum.com/showthread...hreadid=540973