Forum Discussion
when row value changes, copy the row value to a new worksheet but also retain each change
good day gentlemen, i needed help, ive been searching to the internet regarding using vba. im a excel noob trying to use vba to copy certain data to another worksheet.i have been trying all the ...
i like your example, but can we keep local date at column b13 and if you could provide full line until column n13 but with the cell O13 to copied the data to the Hour sheet?
and one more thing sir, i need the same thing to be done on line 12 and 14 also on same hour sheet but not mix it up with each other? thank you very much sir
and one more thing sir, i need the same thing to be done on line 12 and 14 also on same hour sheet but not mix it up with each other? thank you very much sir
Private Sub Worksheet_Change(ByVal Target As Range)
Dim rngBereich As Range
Dim i, j As Long
Set rngBereich = Range("B12:B14")
If Target.Cells.Count > 1 Then GoTo done
If Not Application.Intersect(Target, rngBereich) Is Nothing Then
If Target.Value <> 0 Then
j = Sheets("HOUR").Range("E" & Rows.Count).End(xlUp).Row
Select Case Target.Cells
Case Is = Range("B13")
For i = 3 To 14
Sheets("HOUR").Cells(j + 1, i).Value = Cells(13, i).Value
Next i
Case Is = Range("B12")
For i = 3 To 14
Sheets("HOUR").Cells(j + 1, i).Value = Cells(12, i).Value
Next i
Case Is = Range("B14")
For i = 3 To 14
Sheets("HOUR").Cells(j + 1, i).Value = Cells(14, i).Value
Next i
Case Else
End Select
Else
End If
End If
done:
Application.EnableEvents = True
Exit Sub
End SubMaybe with this code. However i'm not sure what you want to do with cell O13. In the example you can select a date in cell B12 and the information from range C12:N12 is copied to the "HOUR" sheet. If you select a date in cell B13 the information from range C13:N13 is copied to the "HOUR" sheet. Accordingly for row 14.
- https://www.dropbox.com/t/k3rjIAAC3Fv9lRnY
I think you got it wrong sir, every date that key on the local date need to be copy behind the staff name and why i need cell O13 is if all the row a fill up, i can just simply type okay on row O13 to copy the data on the Hour sheet