Search code examples
excelvbauserform

Protecting a new excel sheet that has been exported

I have the following macro that exports a current excel sheet with some data to a new workbook into a specific path. The trouble i have, is that i want to protect that workbook new sheet after is created. How it can be done? I tried using ActiveWorkbook.Protect "Password" but did not worked.

Sub NuevoDia()
Dim FilePath As String
Dim NewName As String

FilePath = "C:\Users\Pol\Desktop\": NewName = FilePath & "Registros " & Format(Date, "DD-MM-YYYY") & ".xls"

Sheets("Registros").Select
Hoja3.Unprotect "LOG2020"
Sheets("Registros").Copy

ActiveWorkbook.SaveAs Filename:=NewName, FileFormat _
:=xlWorkbookNormal, CreateBackup:=False

End Sub

Thanks for the help!

Solution

  • To protect a sheet I would suggest to do : 

    Sheets("Registros").Protect "password"

    And if you wanted to protect workbook since you tried : 

    ActiveWorkbook.Protect Password:="password", Structure:=True, Windows:=True

    Also note that it is better to not use select so 

    Sheets("Registros").Select
Hoja3.Unprotect "LOG2020"
Sheets("Registros").Copy

    do the same as 

    Hoja3.Unprotect "LOG2020"
Sheets("Registros").Copy