Want to unprotect multiple sheets then protect after this code

[Caveman1964]

Hi All,
I made this code using recording. It seems sloppy. I need protect and unprotect BUT.....if you gurus see a better way to write this whole thing......you know what...I'll take it!
Basically, the macro is this,
copying row 7 from sheet 4
pasting row 7 into row 7 into sheet 3
copying info from sheet 2 E5:E17
Pasting info and transposing info the new row of sheet 3

I need to unprotect at beginning of this scenario all sheets and then protect at end.
I would most appreciate any help I can get.
Below is the current code. I recorded it....I don't know how to write this.

Sub SubmitDataNewEntry()

Sheets("Data Collection").Select
Rows("7:7").Select
Selection.Insert Shift:=xlDown, CopyOrigin:=xlFormatFromLeftOrAbove
ActiveSheet.Unprotect "1"
Sheets("DO NOT ALTER").Select
Range("A7:BS7").Select
Selection.Copy
Sheets("Data Collection").Select
Range("A7").Select
ActiveSheet.Paste
Range("A7").Select
Sheets("Complaint Entry").Select
Range("E5:E17").Select
Application.CutCopyMode = False
Selection.Copy
Sheets("Data Collection").Select
Range("A7").Select
Selection.PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
False, Transpose:=True
Range("A7").Select
Sheets("Complaint Entry").Select
Application.CutCopyMode = False
Selection.ClearContents
Range("E5").Select

End Sub

 

[Logit]

.
Here is one method. The password is PW

Option Explicit


Sub CpyPasteSht3n4()
    Sheets("Sheet3").Range("A7").EntireRow.Value = Sheets("Sheet4").Range("A7").EntireRow.Value
    CpyPasteSht2n3
End Sub


Sub CpyPasteSht2n3()
  Application.ScreenUpdating = False
  Dim copySheet As Worksheet
  Dim pasteSheet As Worksheet
  Dim lastrow As Long


  Set copySheet = Worksheets("Sheet2")
  Set pasteSheet = Worksheets("Sheet3")
  lastrow = pasteSheet.Cells(Rows.Count, 1).End(xlUp).Row


  copySheet.Range("E5:E17").Copy
  pasteSheet.Cells(lastrow + 1, 1).PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks:=False, Transpose:=True


  Application.CutCopyMode = False
  Application.ScreenUpdating = True
    
  ProtectWS
End Sub


Sub UnProtectWS()
    Dim ws As Worksheet
    Dim WSArray As Variant
    Dim password As String


    Set WSArray = Sheets(Array("sheet2", "sheet3", "sheet4"))
    For Each ws In WSArray
         ws.Unprotect password:="pw"
    Next
    CpyPasteSht3n4
End Sub


Sub ProtectWS()
    Dim ws As Worksheet
    Dim WSArray As Variant
    Dim password As String


    Set WSArray = Sheets(Array("sheet2", "sheet3", "sheet4"))
    For Each ws In WSArray
         ws.Protect password:="pw"
    Next
End Sub

Download workbook : https://www.amazon.com/clouddrive/share/c0EYytmiqpaNXP3dIIdtpdRts53u84NpEvIu01I7WEo

 

[Caveman1964]

Great thanks!
these work great. Is there a way to make all of this one macro?

 

[Logit]

.
"Is there a way to make all of this one macro?" Because ...... ???


You can just paste everything into a single sub, eliminating some of the duplicate statements as needed.

 

[Caveman1964]

Thank you Logit!
I appreciate you spending your personal time on my problem.

 

[Logit]

You are welcome. Glad to help.