Protecting All Files in Filepath/Protecting all sheets in workbook prior to saving as?

[khlau]

Hi All,

Wondering if someone could point me in the right direction and help please - I have a workbook that I generate other workbooks from, so from the dataset in the main tab a macro sorts the data and then for each name, generates another tab with all the respective data on. For example, "Joe Bloggs, and the report would list all of Joe Bloggs product lists".

Then, using another subroutine, I save each individual tab as it's own workbook. e.g Joe Blogg, Joe Bloggs 2 etc. This has worked well for my purpose, however I am looking to add a layer of user control by protecting certain cells etc. Is there a way to lock Sheet1 in ALL my generated files without doing it manually via VBA?

The generated file only has 1 tab in the file. Thanks for your time.

 

[DanteAmor]

Hi @khlau,

Try this.
Change data in red for your information.

Sub Protecting_All_Files()
  Dim fPath As String, fFiles, wb As Workbook
  fPath = "[COLOR=#ff0000]C:\trabajo\books\[/COLOR]"
  fFiles = Dir(fPath & "*.xls*")
  Do While fFiles <> ""
    Set wb = Workbooks.Open(fPath & fFiles)
    wb.Sheets(1).Unprotect "[COLOR=#ff0000]abc[/COLOR]"
    wb.Sheets(1).Range("[COLOR=#ff0000]A2:D10, E3:D5[/COLOR]").Locked = True
    wb.Sheets(1).Protect "[COLOR=#ff0000]abc[/COLOR]"
    wb.Close True
    fFiles = Dir()
  Loop
End Sub