Works perfectly, thank you!

You are welcome

Will add it to the website this week
Maybe also useful for others

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ladymuck" wrote in message ...
Works perfectly, thank you!

"Ron de Bruin" wrote:

Hi Ladymuck

You can use the add-in
http://www.rondebruin.nl/merge.htm

Or add browse code (untested)
For others example workbook is on this page
http://www.rondebruin.nl/fso.htm

Sub RDB_Merge_Data()
Dim myFiles As Variant
Dim myCountOfFiles As Long
Dim oApp As Object
Dim oFolder As Variant

Set oApp = CreateObject("Shell.Application")

'Browse to the folder
Set oFolder = oApp.BrowseForFolder(0, "Select folder", 512)
If Not oFolder Is Nothing Then

myCountOfFiles = Get_File_Names( _
MyPath:=oFolder.Self.Path, _
Subfolders:=False, _
ExtStr:="*.xl*", _
myReturnedFiles:=myFiles)

If myCountOfFiles = 0 Then
MsgBox "No files that match the ExtStr in this folder"
Exit Sub
End If

Get_Data _
FileNameInA:=True, _
PasteAsValues:=True, _
SourceShName:="", _
SourceShIndex:=1, _
SourceRng:="A1:G1", _
StartCell:="", _
myReturnedFiles:=myFiles

End If

End Sub

--

Regards Ron de Bruin
http://www.rondebruin.nl/tips.htm


"Ladymuck" wrote in message ...
I'm using Ron de Bruin's great bit of code to compile data. However, I need
the MyPath value to be dynamically generated as the source folder will vary
according to the user running the macro. How would I amend the code so that
it does not rely on a hard coded value?

Many thanks!