User Form - auto reset and copy

[JCHuysamer]

I have created a Form that populates a worksheet(ws) - when I click Save & Next (CommandButton1) the data is pasted to the ws but the values I entered on the form does not reset.

​

I only require some of the values to reset on the Form(irow 2 to irow 25)
​

And my lookup does not work either ( it takes data from the active ws (irow 2) and should do a lookup to populate irow 3 (on a diferent ws)


And if possible I will also need a code to copy all new data to a different Workbook on exit/close and delete all information that was entered (ie: reset the workbook)(this will be a shared workbook for users to imput data to a "Master File"

---

​

Private Sub CommandButton1_Click()


Dim iRow As Long
Dim ws As Worksheet
Set ws = Worksheets("Project List")
'ActiveSheet.Unprotect "7319"
iRow = ws.Cells.Find(what:="*", searchorder:=xlRows, searchdirection:=xlPrevious, LookIn:=xlValues).Row + 1


ws.Cells(iRow, 1).Value = Me.DTPicker1
ws.Cells(iRow, 2).Value = Me.CboPN
'ws.Cells(iRow, 3).Value = "=lookup($b$" & iRow & ",'Project Data'!(A:A),'Project Data'!(B:B))"
'ws.Cells(iRow, 4).Value =
ws.Cells(iRow, 5).Value = Me.CboPM
ws.Cells(iRow, 6).Value = Me.CboCOW
ws.Cells(iRow, 7).Value = Int(Me.txtComplianceAudit)
ws.Cells(iRow, 8).Value = Int(Me.txtJTO)
ws.Cells(iRow, 9).Value = Int(Me.txtPI)
ws.Cells(iRow, 10).Value = Int(Me.txtTBT)
ws.Cells(iRow, 11).Value = Int(Me.PplOnSiteSun)
ws.Cells(iRow, 12).Value = Int(Me.PplOnSiteMon)
ws.Cells(iRow, 13).Value = Int(Me.PplOnSiteTue)
ws.Cells(iRow, 14).Value = Int(Me.PplOnSiteWed)
ws.Cells(iRow, 15).Value = Int(Me.PplOnSiteThu)
ws.Cells(iRow, 16).Value = Int(Me.PplOnSiteFri)
ws.Cells(iRow, 17).Value = Int(Me.PplOnSiteSat)
ws.Cells(iRow, 18).Value = "=MAX($K$" & iRow & ":$Q$" & iRow & ")"
ws.Cells(iRow, 19).Value = Int(Me.HoursWorkedSun)
ws.Cells(iRow, 20).Value = Int(Me.HoursWorkedMon)
ws.Cells(iRow, 21).Value = Int(Me.HoursWorkedTue)
ws.Cells(iRow, 22).Value = Int(Me.HoursWorkedWed)
ws.Cells(iRow, 23).Value = Int(Me.HoursWorkedThu)
ws.Cells(iRow, 24).Value = Int(Me.HoursWorkedFri)
ws.Cells(iRow, 25).Value = Int(Me.HoursWorkedSat)
ws.Cells(iRow, 26).Value = "=SUM($S$" & iRow & ":$Y$" & iRow & ")"
'ws.Cells(iRow, 26).Value =
'ws.Cells(iRow, 27).Value =
'ws.Cells(iRow, 28).Value =
'ws.Cells(iRow, 29).Value =
'ActiveSheet.Protect "7319"


End Sub