First, I slipped the password into column B, moving the sheet names over to column C. Then this should do what you've described:
Option Explicit
Private Sub CommandButton1_Click()
Dim myPATHs As Range, myP As Range
Dim mySheets As Range, mySh As Range
Dim fNAME As String, wb As Workbook, Pwd As String
Set myPATHs = Me.Range("A2", Range("A" & Rows.Count).End(xlUp))
Set mySheets = Me.Range("C2", Range("C" & Rows.Count).End(xlUp))
Application.ScreenUpdating = False
For Each myP In myPATHs
Pwd = myP.Offset(, 1)
fNAME = Dir(myP & "*.xl*")
Do While Len(fNAME) > 0
Set wb = Workbooks.Open(myP & fNAME)
For Each mySh In mySheets
With wb.Sheets(mySh.Value)
.Unprotect Pwd
.Cells.Clear
ThisWorkbook.Sheets(mySh.Value).Cells.Copy .Range("A1")
.Protect Pwd
End With
Next mySh
wb.Close True
fNAME = Dir
Loop
Next myP
Application.ScreenUpdating = True
End Sub
Bookmarks