VBA: Copy/Past in new sheet including locking cells

Question

The Situation is this:

I have an Excel sheet, with Input workbook to fill in personal things and for every single month a workbook. Now, there is a button, when the user want to have a new Excel sheet, for reason of changing workplace.

So for example: Mr. Dean has filled in his daily hour work till April the 15. Now he move on the another Location and presses the button to hhange the worklocation. He gets a new Excel sheet, which exactely looks the same as before, filled in with the data that he wrote in from the first sheet (copy/paste from vba-sript). The existing entries (cells) till 15.April are also locked, so that he cant edit them. He continues to write down his hours for the 16. April and so on.

I think it is better, if the second Excel sheet gets created by the macro (copy/paste).

Name of the first sheet: sheet1 Name of the second sheet: sheet2

So my code doesnt worke so far:

Sub Test()
Dim ws1 As Worksheet
Set ws1 = sheet1.Worksheets("Master")
ws1.Copy sheet2.Sheets(Sheets.Count)
End Sub

That will copy the whole sheet?

This code is for Password protection for the woorkbooks. How can I lock just single cells till April 15? So how do I have to read out the date?

Sub sbProtectAllSheets()

    Dim pwd1 As String, pwd2 As String
    pwd1 = InputBox("Please Enter the password")
    If pwd1 = "" Then Exit Sub
    pwd2 = InputBox("Please re-enter the password")

    If pwd2 = "" Then Exit Sub

     'Check if both the passwords are identical
    If InStr(1, pwd2, pwd1, 0) = 0 Or _
    InStr(1, pwd1, pwd2, 0) = 0 Then
        MsgBox "You entered different passwords. No action taken"
        Exit Sub
    End If

    For Each ws In Worksheets
        ws.Protect Password:=pwd1
    Next

    MsgBox "All sheets Protected."

    Exit Sub

End Sub

Or do you have any other suggestions?

Greets Duc

Answer 1

I assume you have rows for each date, and that they follow each other. In the column the hours are entered you can find out the first one that is empty. I wrote this code for that a while back. Fill for +column+ the number of the column to which the hours are entered.

LastRowR = ActiveSheet.UsedRange.Rows.Count
    For row = 2 To LastRowR
        If ActiveSheet.Cells(row, +column+) = "" Then
            LastRowR = row - 1
            Exit For
        End If
    Next row

Then you can lock the range of cells that are in the past. So you can make a range from ("A1":Cells(LastRowR,+LastColumn+)) In which +LastColumn+ is the number of the last column that is used in your sheet.