I am consolidating data from a group of files-the # may keep
increasing/varying.
I want to create a macro that-
asks the user to specify the folder where all these files are located;
once user selects folder, the macro should iterate thru each file and copy
corresponding values onto one master file.
The FileDialogue function doesn't help much...any other function that can
ask folder name and open each file one by one by itself?

Thanks

Here is one way

Option Explicit

Dim oFSO As Object
Dim oWks As Worksheet

Sub LoopFolders()
Dim sStartFolder As String

With Application.FileDialog(msoFileDialogFolderPicker)
.Show
On Error Resume Next
sStartFolder = .SelectedItems(1)
On Error GoTo 0
End With

If sStartFolder < "" Then
Set oWks = ActiveSheet
Set oFSO = CreateObject("Scripting.FileSystemObject")

selectFiles "c:\MyTest"

Set oFSO = Nothing
End If

End Sub


'---------------------------------------------------------------------------
Sub selectFiles(sPath)
'---------------------------------------------------------------------------
Dim Folder As Object
Dim Files As Object
Dim file As Object
Dim fldr As Object
Dim iLastRow As Long

Set Folder = oFSO.GetFolder(sPath)

For Each fldr In Folder.Subfolders
selectFiles fldr.Path
Next fldr

For Each file In Folder.Files
If file.Type = "Microsoft Excel Worksheet" Then
Workbooks.Open Filename:=file.Path
iLastRow = oWks.Cells(Rows.Count, "A").End(xlUp).Row
If iLastRow < 1 Or oWks.Range("A1").Value < "" Then
iLastRow = iLastRow + 1
End If
activehseet.Range("A1:A10").Copy _
Destination:=oWks.Range("A" & iLastRow)
End If
Next file

End Sub

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

"Minerva" wrote in message
...
I am consolidating data from a group of files-the # may keep
increasing/varying.
I want to create a macro that-
asks the user to specify the folder where all these files are located;
once user selects folder, the macro should iterate thru each file and copy
corresponding values onto one master file.
The FileDialogue function doesn't help much...any other function that can
ask folder name and open each file one by one by itself?

Thanks