Using VBA to lock 2x secondary sheets, while allowing Primary sheet to extract information while protected

Question

I would like to use VBA to protect 2 secondary (Project)(Service) sheets using the same password, but allowing the Primary (Comprehensive) sheet to extract information from those sheets while protected.

I am currently using the below to protect the workbook as I need it to auto protect when the workbook is closed.

Private Sub Workbook_BeforeClose(Cancel As Boolean)

   Dim xSheet As Worksheet
   Dim xPsw As String
   xPsw = "####"
   For Each xSheet In Worksheets
   xSheet.Protect xPsw
   Next
   End Sub

Secondary (Project)

and i am using this to extract the information from those sheets, (Project)(Service)

Sub copyFormat()

 
    With ThisWorkbook
        .Worksheets("Project").Range("D9:TJ28").Copy
        .Worksheets("Comprehensive").Range("D10:TJ29").PasteSpecial xlPasteFormats
        .Worksheets("Service").Range("D9:TJ28").Copy
        .Worksheets("Comprehensive").Range("D30:TJ49").PasteSpecial xlPasteFormats
    End With
 
 With ThisWorkbook
        .Worksheets("Project").Range("A9:C28").Copy
        .Worksheets("Comprehensive").Range("A10:C29").PasteSpecial
        .Worksheets("Service").Range("A9:C28").Copy
        .Worksheets("Comprehensive").Range("A30:C49").PasteSpecial
    End With

End Sub

As i would like everyone to have access the Primary (Comprehensive) sheet, to hit the "update" button with the assigned macro to extract the most UpToDate information from the secondary sheets (project)(service) without having to unlock them.

Primary (Comprehensive)

Any assistance would be greatly appreciated thankyou

Please see above details my current document information

Answer 1

You have to add the unprotect/protect to your macro:

Sub copyFormat()
Dim xPsw As String
   xPsw = "####"
 
    With ThisWorkbook

        .Worksheets("Comprehensive").Unprotect xPsw   '<---- add this line of code

        .Worksheets("Project").Range("D9:TJ28").Copy
        .Worksheets("Comprehensive").Range("D10:TJ29").PasteSpecial xlPasteFormats
        .Worksheets("Service").Range("D9:TJ28").Copy
        .Worksheets("Comprehensive").Range("D30:TJ49").PasteSpecial xlPasteFormats
    End With
 
    With ThisWorkbook
        .Worksheets("Project").Range("A9:C28").Copy
        .Worksheets("Comprehensive").Range("A10:C29").PasteSpecial
        .Worksheets("Service").Range("A9:C28").Copy
        .Worksheets("Comprehensive").Range("A30:C49").PasteSpecial

        .Worksheets("Comprehensive").Protect xPsw   '<---- add this line of code


    End With

End Sub