Copy range from closed excel workbook into master file + finding next empty row

[ralphmeys]

Hi guys,

i'm a real novice in VBA programming and could really use your help

currently i am setting up a project data base where people fill in a project file. This file is then saved into H:\Medical\Project management\Projects. then in my overview file a selected amount of cells are then copies from those project files. Now I found a code and adjust it to find this directory and scan for all the "xl*" files. then it copies a range of cells (A2:J2) to the overview file starting at "A5".

now I have 2 problems
1) every time I have to select the "xl*" file I want the code to cope paste the desired range (A2:J2) into my overview file starting at (A5). I would like the code to find all "xl*" files and take A2:J2 from every file, every time I run the code
2) currently the code keeps rewriting into Cell A5 and doesn't find the next empty row.

This is the code i have so far. Could you guys help me with that?

Thanks!

Sub GetData_Example4()
    Dim SaveDriveDir As String, MyPath As String
    Dim FName As Variant

    SaveDriveDir = CurDir
    MyPath = "H:\Medical\Project management\Projects"   'or use "C:\Data"
    ChDrive MyPath
    ChDir MyPath
    FName = Application.GetOpenFilename(filefilter:="Excel Files, *.xl*")

    If FName = False Then
        'do nothing
    Else
        GetData FName, "Sheet1", "A2:J2", Sheets("Sheet1").Range("A5"), False, False
    End If

    ChDrive SaveDriveDir
    ChDir SaveDriveDir
End Sub 

[patel45]

you missed sub getdata