when row value changes, copy the row value to a new worksheet but also retain each change

[ajiespedas]

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 vba line on the internet, and i cant seem to make it work like it suppose to. so Im stuck!! please help me  based on the picture i shared, local date dictate for a line 13 from (C13 to N13) to be copy to another worksheet (HOUR).each and everytime local date change in relation to the line 13, it will copy to HOUR worksheet to the next line without overwriting the previous one.
example

local date 01/01/23-shane bunyak , 00;40 and so on till line N13
local date 02/01/23-danial ,00;30 and so on till line N13

please help me, i have come to a dead end with my noob knowledge.

[#2]

@ajiespedas 

Private Sub Worksheet_Change(ByVal Target As Range)

Dim rngBereich As Range
Dim i, j As Long

Set rngBereich = Range("H6")

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

For i = 3 To 14
Sheets("HOUR").Cells(j + 1, i).Value = Cells(13, i).Value
Next i
Else
End If

End If

done:
Application.EnableEvents = True
Exit Sub


End Sub

You can try the Worksheet_Change event. In the attached file you can update data in range C13:N13. If you then change the date in cell H6 the data is copied to sheet "HOUR".

[#3]

@OliverScheurichthanks for the reply sir, let me have a try and will update ya with the result.

by the way, thanks for replying, means alot

[#4]

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

[#5]

@ajiespedas 

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 Sub

Maybe 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.

[#6]

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