Hello,

I am not an experienced coder, and have been wrestling to create a Macro that will copy a Template sheet and create new sheets in the same Workbook, and named from a Template cell, and to sequence the sheet naming, and also lock down the copied sheets with a PW. I had a Copy Range macro developed, but there seems to be no way to bring across the "Print" settings and the "Header" information with Range Copy, or if there is I can't find the info anywhere.

So the approach that I took is copy the whole Template sheet and delete the parts that are not needed, outside of the Print Area. This is the Code that I found and modified to delete unwanted Columns. I have not been able to insert PW protect code anywhere in the string without some bug cropping up. (mostly not protecting the first and second copies, and stopping the run past 4 copies) Any help with adding PW protect coding in this Sub would be appreciated.

Thanks,
Tom McLernon

Sub NewSheet()
Dim ws As Worksheet
SheetName = Sheets("Complete the New LEM Here").Range("K1")

For Each ws In ActiveWorkbook.Worksheets
If ws.Name = SheetName Then GoTo exists1:
Next
ActiveSheet.Copy _
after:=Sheets(Sheets.Count)
ActiveSheet.Name = SheetName
Columns("M:Q").EntireColumn.Delete
Exit Sub
exists1:
For Each ws In ActiveWorkbook.Worksheets
If Left(ws.Name, Len(SheetName) + 1) = SheetName & "-" Then GoTo exists2:
Next
ActiveSheet.Copy _
after:=Sheets(Sheets.Count)
ActiveSheet.Name = SheetName & "-1"
Exit Sub
exists2:
n = 0
For Each ws In ActiveWorkbook.Worksheets
If Left(ws.Name, Len(SheetName)) = SheetName And Len(ws.Name) > Len(SheetName) + 1 Then
If Left(ws.Name, Len(SheetName)) = SheetName And Right(ws.Name, Len(ws.Name) - (Len(SheetName) + 1)) * 1 > n Then
n = Right(ws.Name, Len(ws.Name) - (Len(SheetName) + 1)) * 1
End If
End If
Next
ActiveSheet.Copy _
after:=Sheets(Sheets.Count) 'ActiveWorkbook.Sheets("Letter-Template")
ActiveSheet.Name = SheetName & "-" & n + 1


End Sub