Hi,

i'm using the code below to download certain data from all the workbooks in a certain folder. However i need to adapt it slightly as the worksheets in each workbook will have different names. The code i currently have makes reference to a specific sheet name...i need this to be adapted so that it just downloads from a sheet with any name in each workbook....is this possible??

Private Sub Workbook_Open()


    Dim MyPath As String
    Dim FilesInPath As String
    Dim sh As Worksheet
    Dim MyFiles() As String
    Dim Fnum As Long
    Dim rnum As Long
    Dim destrange As Range
 
    MyPath = "File Location"    ' <<<<  Change
 
    'Add a slash at the end if the user forget it
    If Right(MyPath, 1) <> "\" Then
        MyPath = MyPath & "\"
    End If
 
    'If there are no Excel files in the folder exit the sub
    FilesInPath = Dir(MyPath & "*.xls")
    If FilesInPath = "" Then
        MsgBox "No files found"
        Exit Sub
    End If
 
    On Error GoTo CleanUp
    Application.ScreenUpdating = False
 
    'Add worksheet to the Activeworkbook and use the Date/Time as name
    Set sh = ActiveWorkbook.Worksheets.Add
    sh.Name = Format(Now, "dd-mm-yy h-mm-ss")
 
    'Fill the array(myFiles)with the list of Excel files in the folder
    Fnum = 0
    Do While FilesInPath <> ""
        Fnum = Fnum + 1
        ReDim Preserve MyFiles(1 To Fnum)
        MyFiles(Fnum) = FilesInPath
        FilesInPath = Dir()
    Loop
 
    'Loop through all files in the array(myFiles)
    If Fnum > 0 Then
        For Fnum = LBound(MyFiles) To UBound(MyFiles)
 
            'Find the last row with data
            rnum = LastRow(sh)
 
            'create the destination cell address
            Set destrange = sh.Cells(rnum + 1, "A")
 
            ' Copy the workbook name in Column E
            sh.Cells(rnum + 1, "E").Value = MyPath & MyFiles(Fnum)
 
            'Get the cell values and copy it in the destrange
            'Change the Sheet name and range as you like
            GetData MyPath & MyFiles(Fnum), ".", "b54:l54", destrange, False, False
        Next
    End If