Hi I have a Workbook with 2 worksheets "Current Staff List" & "Historical Staff List" I have a user form to edit employee information and this extracts the information based on the employee number from the Current Staff List. When I save any changes the current staff list is updated correctly, but where i am struggling is to write the code to find the employee number on the Historical staff sheet and update any changes on to that sheet. Any help would be appreciated Regards Bob
VBA Code:
Private Sub CommandButton1_Click()
Dim i As Long, LastRow As Long
Set sh = ThisWorkbook.Sheets("Current Staff List")
Set sh2 = ThisWorkbook.Sheets("Historical Staff List")
Dim n As Long
Dim m As Long
LastRow = Sheets("Current Staff List").Range("b" & Rows.Count).End(xlUp).Row
For i = 4 To LastRow
If Sheets("Current Staff List").Cells(i, 2).Value = (Me.ComboBox6) Or _
Sheets("Current Staff List").Cells(i, 2).Value = Val(Me.ComboBox6) Then
sh.Unprotect "office"
'Sheets("Current Staff List").Cells(i, 2) = entbox.Text
Sheets("Current Staff List").Cells(i, 3) = Combobox1.Text
Sheets("Current Staff List").Cells(i, 1) = ComboBox4.Text
Sheets("Current Staff List").Cells(i, 4) = ComboBox5.Text
Sheets("Current Staff List").Cells(i, 5) = fntbox.Text
Sheets("Current Staff List").Cells(i, 6) = sntbox.Text
Sheets("Current Staff List").Cells(i, 8) = dobtbox.Text
Sheets("Current Staff List").Cells(i, 16) = add1tbox.Text
Sheets("Current Staff List").Cells(i, 17) = add2tbox.Text
Sheets("Current Staff List").Cells(i, 18) = add3tbox.Text
Sheets("Current Staff List").Cells(i, 19) = pctbox.Text
Sheets("Current Staff List").Cells(i, 20) = con1tbox.Text
Sheets("Current Staff List").Cells(i, 21) = con2tbox.Text
Sheets("Current Staff List").Cells(i, 22) = mailtbox.Text
Sheets("Current Staff List").Cells(i, 9) = nitbox.Text
Sheets("Current Staff List").Cells(i, 23) = sdtbox.Text
Sheets("Current Staff List").Cells(i, 24) = hrstbox.Text
Sheets("Current Staff List").Cells(i, 26) = paytbox.Text
Sheets("Current Staff List").Cells(i, 25) = holtbox.Text
Sheets("Current Staff List").Cells(i, 30) = bontbox.Text
Sheets("Current Staff List").Cells(i, 10) = distbox.Text
Sheets("Current Staff List").Cells(i, 11) = pintbox.Text
Sheets("Current Staff List").Cells(i, 12) = pinxtbox.Text
Sheets("Current Staff List").Cells(i, 13) = pastbox.Text
Sheets("Current Staff List").Cells(i, 14) = passxtbox.Text
MsgBox "The information has been amended", , "Health Care"
With ActiveSheet
.Protect Password:="office", AllowFiltering:=True
.EnableSelection = xlockedCells
End With
End If
Next
End Sub