Hi all,

I have a directory folder with an active workbook and another workbook id like to copy data from. The Following macro opens up a file in the same directory, copies some data and pastes it in the active workbook. However with this code I have to specify the filename, 'Data.xlsm' in the example code. I would like it to copy data from the only other workbook in the current directory WITHOUT having to specify the name in the code, so just opening it up no matter what filename it has.

In addition I would like to extract the filename from the workbook im copying data from and paste it into the activewoorkbook in sheet 1 Cell A1. I had a look at getopenfilename function but cant seem to make it work for my purpose.

Thanks very much!

Sub ImportData()
Dim FilePath As String
Dim FileName As String
Dim dataWbk As Excel.Workbook
Dim simWbk As Excel.Workbook
Dim simSht As Worksheet

Set simWbk = ActiveWorkbook
Set simSht = ActiveSheet

Dim sPath As String
sPath = ActiveWorkbook.Path

FilePath = (sPath & "\Data")

Set dataWbk = Workbooks.Open(FilePath & ".xlsm")
dataWbk.Sheets(1).Range("A1:L59").Select
Selection.Copy

simWbk.Sheets(4).Activate
simWbk.Sheets(4).Range("B1").Select
ActiveSheet.Paste
dataWbk.Close False
Worksheets(1).Activate
End Sub