VBA Open and Copy From Multiple Files

[hobbiton73]

Hi, I wonder whether someone may be able to help me please.

I'm trying to put together a script which performs the following function:

[LIST]Create a dialog box which allows the user to select a folder and automatically open multiple 'Source' files which all have the password of "Master".[/LIST]

• From each 'Source' file, open the sheet called "Input" start at row 2 and copy from columns A:L down to the last used row. Then close each 'Source' file.

• Paste the data from each 'Source' file to the sheet "All Data" contained with the 'Destination file where the macro is being run from, pasting the rows of data one under another in columns B:M starting at row 5

I've managed to put together the first part which allows the user to select the folder and open the files which is as below:

Sub OpenMultipleFilesFromFolder2()
     
    Dim fPath As String, mName As String, fName As String
     
    fPath = "C:Reporting\Chris Test\"
    mName = InputBox(Prompt:="Please Type Month Name", Title:="Month Folder")
    If mName = vbNullString Then
        MsgBox "Missing Month Info"
        Exit Sub
    End If
     
    fPath = fPath & "\" & mName & "\"
    fName = Dir(fPath & "\*.xls*")
    Do While fName <> ""
        Workbooks.Open Filename:=fPath & "\" & fName
        fName = Dir
    Loop
     
End Sub

But I'm having a little difficulty in incorporating the password which would allow the files to open automatically, and I'm unsure how to put together the copy and paste functionality.

I just wondered whether someone could possibly look at this please and offer some guidance on how I may go about achieving this.

Many thanks and kind regards