copy information in a row,after value changes(to another worksheet)

[AJIESPEDAS]

good day gentlemen,
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 leaving to register to mr.excel to get help.
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.

 

[RudRud]

mine got this when i double click on the column O12, highlight on the arrow that i draw

Hi, Sorry for the inconvenience, i didnt think the dictionary exist properly. here's the revise code

All are possible for adjustment , only the conditions needed to state well so i can re-adjust it.

existing name with the same date, what're you going to deal with it? do you want to copy to the next row? or overwrite the existing values through the new one? for now the code is doing nothing with it

Thanks for bear with me, looking forward to hear you soon

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim dict As New Dictionary

 B = Range(Cells(Target.Row, "C"), Cells(Target.Row, Target.Column - 1)).Value
 
With Sheets("hour")
A = .Range("b3:l" & .Cells(Rows.Count, "a").End(xlUp).Row).Value
End With

For i = 1 To UBound(A, 1)
If Not dict.Exists(A(i, 2)) And A(i, 2) <> "" Then
    dict.Add A(i, 1) & A(i, 2), i
End If
Next i

ld = Sheets("voyage report").Range("h6").Value

    If B(1, 1) <> "" Then
 
        If Not dict.Exists(ld & B(1, 1)) Then
        With Sheets("hour")
            lrow = .Cells(Rows.Count, "g").End(xlUp).Row + 1
             .Cells(lrow, "a").Value = "LOCAL DATE"
            .Cells(lrow, "b").Value = ld
            .Cells(lrow, "c").Resize(1, UBound(B, 2)).Value = B
        End With
        End If
 
    End If
End Sub

 

[AJIESPEDAS]

Hi, Sorry for the inconvenience, i didnt think the dictionary exist properly. here's the revise code

All are possible for adjustment , only the conditions needed to state well so i can re-adjust it.

existing name with the same date, what're you going to deal with it? do you want to copy to the next row? or overwrite the existing values through the new one? for now the code is doing nothing with it

Thanks for bear with me, looking forward to hear you soon

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim dict As New Dictionary

 B = Range(Cells(Target.Row, "C"), Cells(Target.Row, Target.Column - 1)).Value
 
With Sheets("hour")
A = .Range("b3:l" & .Cells(Rows.Count, "a").End(xlUp).Row).Value
End With

For i = 1 To UBound(A, 1)
If Not dict.Exists(A(i, 2)) And A(i, 2) <> "" Then
    dict.Add A(i, 1) & A(i, 2), i
End If
Next i

ld = Sheets("voyage report").Range("h6").Value

    If B(1, 1) <> "" Then
 
        If Not dict.Exists(ld & B(1, 1)) Then
        With Sheets("hour")
            lrow = .Cells(Rows.Count, "g").End(xlUp).Row + 1
             .Cells(lrow, "a").Value = "LOCAL DATE"
            .Cells(lrow, "b").Value = ld
            .Cells(lrow, "c").Resize(1, UBound(B, 2)).Value = B
        End With
        End If
 
    End If
End Sub

hi, i tried the the newest code that you provide getting closer and closer to perfection. i just found out that the row 15 still getting overwrite; and not properly copy the value. < and when copy value jumble up it does not overwriting the previous data, it only happen on row15,the last one


>>existing name with the same date, what're you going to deal with it? do you want to copy to the next row? or overwrite the existing values through the new one? for now the code is doing nothing with it <-i think overwriting the existing value make it looks profesional.

 

[RudRud]

Hi, Please try the code below. Please do make sure the formats starting from row 3 below column A to N (Hour sheets) are same

vr fixup (1).xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			STAFF NO	STAFF NAME			P1		P2		P1/US		TOTAL
2							DAY	NIGHT	DAY	NIGHT	DAY	NIGHT
3	LOCAL DATE	5/1/2023	FO0096	THARMA RAJAH			00:01	00:02	00:20	00:20	00:02	02:31	ER1
4	LOCAL DATE	5/1/2023	FO0120	ABDUL AZIZ B AMRAN			00:40	00:07	00:00	00:09	00:10	312:31	ER2
5	LOCAL DATE	5/1/2023	0	DATO SAKHTIAR			00:01		00:00			32:32	ER3
6	LOCAL DATE	5/1/2023	0	MOHD ALIZAN			00:02	01:23	00:00			312:31	ER4
7	LOCAL DATE	6/1/2023	FO0096	THARMA RAJAH			00:01	00:02	00:20	00:20	00:02	02:31	TEST1
8	LOCAL DATE	6/1/2023	FO0120	ABDUL AZIZ B AMRAN			00:40	00:07	00:00	00:09	00:10	312:31	TEST12
9	LOCAL DATE	6/1/2023	0	DATO SAKHTIAR			00:01		00:00			32:32	TEST3
10	LOCAL DATE	6/1/2023	0	MOHD ALIZAN			00:02	01:23	00:00			312:31	TEST4
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
HOUR

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim dict As New Dictionary

 B = Range(Cells(Target.Row, "C"), Cells(Target.Row, Target.Column - 1)).Value
 
With Sheets("hour")
A = .Range("b3:l" & .Cells(Rows.Count, "a").End(xlUp).Row).Value
End With

For i = 1 To UBound(A, 1)
If Not dict.Exists(A(i, 3)) And A(i, 3) <> "" Then
    dict.Add A(i, 1) & A(i, 3), i + 2
End If
Next i

ld = Sheets("voyage report").Range("h6").Value

    If B(1, 1) <> "" Then
 
        If Not dict.Exists(ld & B(1, 2)) Then
        lrow = Sheets("hour").Cells(Rows.Count, "g").End(xlUp).Row + 1
        Else
        lrow = dict(ld & B(1, 2))
        End If
      
        With Sheets("hour")
         .Cells(lrow, "a").Value = "LOCAL DATE"
        .Cells(lrow, "b").Value = ld
        .Cells(lrow, "c").Resize(1, UBound(B, 2)).Value = B
         End With
    End If

End Sub

 

[AJIESPEDAS]

Hi, Please try the code below. Please do make sure the formats starting from row 3 below column A to N (Hour sheets) are same

View attachment 93019

vr fixup (1).xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1			STAFF NO	STAFF NAME			P1		P2		P1/US		TOTAL
2							DAY	NIGHT	DAY	NIGHT	DAY	NIGHT
3	LOCAL DATE	5/1/2023	FO0096	THARMA RAJAH			00:01	00:02	00:20	00:20	00:02	02:31	ER1
4	LOCAL DATE	5/1/2023	FO0120	ABDUL AZIZ B AMRAN			00:40	00:07	00:00	00:09	00:10	312:31	ER2
5	LOCAL DATE	5/1/2023	0	DATO SAKHTIAR			00:01		00:00			32:32	ER3
6	LOCAL DATE	5/1/2023	0	MOHD ALIZAN			00:02	01:23	00:00			312:31	ER4
7	LOCAL DATE	6/1/2023	FO0096	THARMA RAJAH			00:01	00:02	00:20	00:20	00:02	02:31	TEST1
8	LOCAL DATE	6/1/2023	FO0120	ABDUL AZIZ B AMRAN			00:40	00:07	00:00	00:09	00:10	312:31	TEST12
9	LOCAL DATE	6/1/2023	0	DATO SAKHTIAR			00:01		00:00			32:32	TEST3
10	LOCAL DATE	6/1/2023	0	MOHD ALIZAN			00:02	01:23	00:00			312:31	TEST4
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
HOUR

Private Sub Worksheet_BeforeDoubleClick(ByVal Target As Range, Cancel As Boolean)

Dim dict As New Dictionary

 B = Range(Cells(Target.Row, "C"), Cells(Target.Row, Target.Column - 1)).Value
 
With Sheets("hour")
A = .Range("b3:l" & .Cells(Rows.Count, "a").End(xlUp).Row).Value
End With

For i = 1 To UBound(A, 1)
If Not dict.Exists(A(i, 3)) And A(i, 3) <> "" Then
    dict.Add A(i, 1) & A(i, 3), i + 2
End If
Next i

ld = Sheets("voyage report").Range("h6").Value

    If B(1, 1) <> "" Then
 
        If Not dict.Exists(ld & B(1, 2)) Then
        lrow = Sheets("hour").Cells(Rows.Count, "g").End(xlUp).Row + 1
        Else
        lrow = dict(ld & B(1, 2))
        End If
     
        With Sheets("hour")
         .Cells(lrow, "a").Value = "LOCAL DATE"
        .Cells(lrow, "b").Value = ld
        .Cells(lrow, "c").Resize(1, UBound(B, 2)).Value = B
         End With
    End If

End Sub

hi RudRud,
i believe i can make it work...thanks a million! for your help...

 

[RudRud]

hi RudRud,
i believe i can make it work...thanks a million! for your help...

Hi @AJIESPEDAS,

Glad to assist, Please don't hesitate to ask/share with us if have anything that we can assist you