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 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.
5 Replies
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 SubYou 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".
- 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 sirPrivate 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.
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
