Cnage VBA code to select filename

[ezonemy]

I have the below VBA code running. However the filename SCORESHEET always changes as my staff’s are required to save the worksheet once they opened it. The macro in this sheet is activated via command button. They will save the filename something like this STAFF NAME-KPI-DDMMYY (e.g. JOHN-KPI-181214). The sheetname would not change. Please advice on how to make the changes in the VBA code below to adopt the changes in filename.</SPAN>

Sub CopyData() 
    Application.ScreenUpdating = False 
    Dim foundDate As Range 
    Dim wb As Workbook 
    Set wb = Workbooks.Open("C:\Users\Mario\Desktop\Forum Help\MASTER.xlsx") 
    Dim ws As Worksheet 
    For Each ws In Workbooks("MASTER.xlsx").Sheets 
        If ws.Name = Workbooks("Scoresheet.xlsm").Sheets("Sheet1").Range("B2").Value Then 
            Set foundDate = ws.Range("A:A").Find(Workbooks("Scoresheet.xlsm").Sheets("Sheet1").Range("E2"), LookIn:=xlValues, lookat:=xlWhole) 
            If Not foundDate Is Nothing Then 
                foundDate.Offset(0, 1) = Workbooks("Scoresheet.xlsm").Sheets("Sheet1").Range("H2") 
            End If 
        End If 
    Next ws 
    wb.Close True 
    Application.ScreenUpdating = True 
End Sub

And also please advice code to autosave the sheet at end of the code above.

Thank you a lot all for your time.

 

[ezonemy]

Also pelase help on code to open filename MASTER as it is password protected to open.