The VBA that I am using to password protect an excel file asks to save a copy of the file while running, when I want it to pass protect the same file

[reubenjacobc]

So to give you some context, I have a bunch of excel files in a folder that I need to password protect with unique passwords.

My objective is to password protect all the existing files in the folder without having to manually open them and apply a password.

below is the code I am using:

With wslist
    
    For i = first_row To last_row - 1
    
    'Worksheets("Processing File").Cells(i, 4) = Worksheets("Home_Page").Cells(i, 5).Value
    
    file_name = Worksheets("Home_Page").Cells(i, 6).Value
    temp_password = Worksheets("Home_Page").Cells(i, 7).Value
    
    'ActiveWorkbook.SaveCopyAs filename:=file_name, FileFormat:=xlOpenXMLWorkbook
    Set masterWB = Workbooks.Open(file_name)
    masterWB.Password = temp_password
    masterWB.Close savechanges:=True
    
    Next i
     
End With

Unfortunately, running this code opens up a dialog box asking me to save a copy of the file. So for example if I have a "File1" in the folder I need to password protect, running this vba asks me to save it as a copy of File1 instead of password protecting the original file. So if I have 20 files I need to encrypt, every time it password protects, excel asks me to "Save a copy for file name :x"

Can anyone tell me what is wrong with the code??

Please help !

 

[Jeffrey Mahoney]

Please try this. I don't if it'll make a difference

With wslist
    
    For i = first_row To last_row - 1
    
    'Worksheets("Processing File").Cells(i, 4) = Worksheets("Home_Page").Cells(i, 5).Value
    
    file_name = Worksheets("Home_Page").Cells(i, 6).Value
    temp_password = Worksheets("Home_Page").Cells(i, 7).Value
    
    'ActiveWorkbook.SaveCopyAs filename:=file_name, FileFormat:=xlOpenXMLWorkbook
    Set masterwb = Workbooks.Open(file_name)
    masterwb.Password = temp_password
    masterwb.Save
    masterwb.Close savechanges:=False
    
    Next i
     
End With