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

A

AJIESPEDAS

New Member
Joined
Jun 3, 2023
Messages
19
Office Version
  1. 2019
Platform
  1. MacOS
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.
 

Attachments

  • Screenshot 2023-06-03 at 5.33.23 PM.png
    Screenshot 2023-06-03 at 5.33.23 PM.png
    98.4 KB · Views: 15
Sort by date Sort by votes
RudRud said:
Hi @AJIESPEDAS, Code will start run If H6 Value is different with previous H6 Value (For example last input 6/7/2023 then you change into 6/8/2023) , Therefore code will start checking row 12 and row 14 see if existing in Hour(sheet), If not exists then will paste into it.

Right click voyage report -> View code -> paste the below code

Normally, You willl change date first then input staff name? or how? see if you need other adjustment :)

View attachment 92873

VBA Code: 
Private Sub Worksheet_Change(ByVal Target As Range)
Dim OldValue As Variant

If Target.Value = "" Then Exit Sub
If Target.Column = 8 And Target.Row = 6 Then 'H6

     Application.EnableEvents = False
     Application.Undo
     OldValue = Target.Value
     Application.Undo
     Application.EnableEvents = True
     If OldValue <> Target.Value And OldValue <> "" Then 'Detect If old and new date are different, If different then run the code
        Call test
     End If
End If

End Sub

Sub test()
Dim dict As New Dictionary

With Sheets("hour")
A = .Range("c2: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)) Then
    dict.Add A(i, 2), i
End If
Next i

B = Sheets("Voyage report").Range("c12:n14").Value 'row 12 - 14

For i = 1 To UBound(B, 1)
    If i <> 2 Then 'except row13 - starting 12 13 14 which is 2
    If Not dict.Exists(B(i, 2)) Then
    With Sheets("hour")
        lrow = .Cells(Rows.Count, "g").End(xlUp).Row + 1
         .Cells(lrow, "a").Value = "LOCAL DATE"
        .Cells(lrow, "b").Value = Sheet1.[h6].Value
        .Cells(lrow, "c").Resize(1, UBound(B, 2)).Value = B
    End With
    End If
    End If
Next i


End Sub
Click to expand...
usually what we do, we fill up the local date, staff name and the hours follow after. when the tasking is complete, this so called VR, will be process to the other department to sort each of the staff flying hours respectively. what im thinking of, by run a value on column o13 for example, will automatically copy to another worksheet (HOUR). but this got me thinking, using mac Os platform, i cant simply link between workbook , some limitation i think. but then again, let me try run your code and try adjust.You been helping me a lot, thank you so much
 
Upvote 0

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
AJIESPEDAS said:
usually what we do, we fill up the local date, staff name and the hours follow after. when the tasking is complete, this so called VR, will be process to the other department to sort each of the staff flying hours respectively. what im thinking of, by run a value on column o13 for example, will automatically copy to another worksheet (HOUR). but this got me thinking, using mac Os platform, i cant simply link between workbook , some limitation i think. but then again, let me try run your code and try adjust.You been helping me a lot, thank you so much
Click to expand...
**after trying the code, when i try to execute,sometimes it will duplicate the information twice for no reason..but here i include a kids drawing i would say for better understanding, i hope..i suck at this,sorry..

by the way, all the information needed to be copy is like wise the red arrow, from voyage report sheet to the hour sheet, regardless position or date of which get to fill up first, but when all the data has been submitted, i would like to have some kind cell value on the same row maybe in column O which the code will run for example O12, to copy the row behind it, and O13 for row behind it and O14 for row behind it as well. for the data collection, maybe we can copy it on the same hour sheet as per picture sent, with its own table for every row, rather than to add extra sheet for it.
hope you understand a little bit better. dont get me wrong, your code is running 95%, just that im not a very good at this vba at all, can even modify without making it worst. yet again, im sorry to trouble you and i cant thank you enough for helping.
 

Attachments

  • Screenshot 2023-06-05 at 1.11.33 AM.jpg
    Screenshot 2023-06-05 at 1.11.33 AM.jpg
    166.1 KB · Views: 4
Upvote 0
AJIESPEDAS said:
**after trying the code, when i try to execute,sometimes it will duplicate the information twice for no reason..but here i include a kids drawing i would say for better understanding, i hope..i suck at this,sorry..

by the way, all the information needed to be copy is like wise the red arrow, from voyage report sheet to the hour sheet, regardless position or date of which get to fill up first, but when all the data has been submitted, i would like to have some kind cell value on the same row maybe in column O which the code will run for example O12, to copy the row behind it, and O13 for row behind it and O14 for row behind it as well. for the data collection, maybe we can copy it on the same hour sheet as per picture sent, with its own table for every row, rather than to add extra sheet for it.
hope you understand a little bit better. dont get me wrong, your code is running 95%, just that im not a very good at this vba at all, can even modify without making it worst. yet again, im sorry to trouble you and i cant thank you enough for helping.
Click to expand...
Hi @*AJIESPEDAS ,

1. When all data has been submitted, Directly copy to hour sheet? Anything else that need to be considered? (Last Code will skip row 13, track if hour sheet got existing name)

this code just directly copy to hour sheet, so you can complete the data first after that run the code. I still dont really get it regarding Column O and the conditions, Maybe you can elaborate more about it. Thank you:)

vr fixup.xlsm
ABCDEFGHIJKLMN
1STAFF NOSTAFF NAMEP1P2P1/USTOTAL
2DAYNIGHTDAYNIGHTDAY NIGHT
3LOCAL DATE3/1/2023FO0096THARMA RAJAH00:0100:0200:2000:2000:0200:0000:45
4LOCAL DATE3/2/2023FO0096THARMA RAJAH00:0100:0200:2000:2000:0200:0000:45
HOUR


As Usual, Right Click Voyage Report sheet -> View code -> Paste code

VBA Code: 
Sub test()

With Sheets("hour")
A = .Range("c2:l" & .Cells(Rows.Count, "a").End(xlUp).Row).Value
End With

B = Sheets("Voyage report").Range("c12:n14").Value 'row 12 - 14

For i = 1 To UBound(B, 1)
    If i <> 2 Then 'except row13 - starting 12 13 14 which is 2
  
    With Sheets("hour")
        If InStr(B(i, 1), "FO") >= 1 And B(i, 2) <> "" Then
            lrow = .Cells(Rows.Count, "g").End(xlUp).Row + 1
             .Cells(lrow, "a").Value = "LOCAL DATE"
            .Cells(lrow, "b").Value = Sheet1.[h6].Value
            .Cells(lrow, "c").Resize(1, UBound(B, 2)).Value = B
           End If
    End With
  
    End If
 
Next i

End Sub
 
Upvote 0
RudRud said:
Hi @*AJIESPEDAS ,

1. When all data has been submitted, Directly copy to hour sheet? Anything else that need to be considered? (Last Code will skip row 13, track if hour sheet got existing name)

this code just directly copy to hour sheet, so you can complete the data first after that run the code. I still dont really get it regarding Column O and the conditions, Maybe you can elaborate more about it. Thank you:)

vr fixup.xlsm
ABCDEFGHIJKLMN
1STAFF NOSTAFF NAMEP1P2P1/USTOTAL
2DAYNIGHTDAYNIGHTDAY NIGHT
3LOCAL DATE3/1/2023FO0096THARMA RAJAH00:0100:0200:2000:2000:0200:0000:45
4LOCAL DATE3/2/2023FO0096THARMA RAJAH00:0100:0200:2000:2000:0200:0000:45
HOUR


As Usual, Right Click Voyage Report sheet -> View code -> Paste code

VBA Code: 
Sub test()

With Sheets("hour")
A = .Range("c2:l" & .Cells(Rows.Count, "a").End(xlUp).Row).Value
End With

B = Sheets("Voyage report").Range("c12:n14").Value 'row 12 - 14

For i = 1 To UBound(B, 1)
    If i <> 2 Then 'except row13 - starting 12 13 14 which is 2
 
    With Sheets("hour")
        If InStr(B(i, 1), "FO") >= 1 And B(i, 2) <> "" Then
            lrow = .Cells(Rows.Count, "g").End(xlUp).Row + 1
             .Cells(lrow, "a").Value = "LOCAL DATE"
            .Cells(lrow, "b").Value = Sheet1.[h6].Value
            .Cells(lrow, "c").Resize(1, UBound(B, 2)).Value = B
           End If
    End With
 
    End If
 
Next i

End Sub
Click to expand...
HI RudRud,

what i meant to say is on the column O is to run the code, now all i did is insert the button to run the code so instead of H6 to run the code.i can make it work thou but, now the code only copy the row 12, how to make it to copy each row 13 and 14? can it be copy seperately? and again sometimes it make 2 copy of the same thing.
 
Last edited:
Upvote 0
AJIESPEDAS said:
HI RudRud,

what i meant to say is on the column O is to run the code, now all i did is insert the button to run the code so instead of H6 to run the code.i can make it work thou but, now the code only copy the row 12, how to make it to copy each row 13 and 14? can it be copy seperately? and again sometimes it make 2 copy of the same thing.
Click to expand...
it comes out this when the staff name on the voyage report sheet remain empty as well.
 

Attachments

  • Screenshot 2023-06-05 at 4.57.31 PM.png
    Screenshot 2023-06-05 at 4.57.31 PM.png
    82.6 KB · Views: 4
Upvote 0
Hi, I might try this again.

Existing names with the same local date will not be copied to hours (sheet), Does it match your criteria? Bcos i still consindering your criteria

Regarding Column O, I can configure double clicking on it so that, for instance, when you click on O12, it will immediately check to see if the staff and local date are already in the hour sheet; if they are not, it will copy the information to the hour sheet. If you click on row 12, will it track row 12 to row 15 or would you prefer to copy each row separately?

vr fixup.xlsm
ABCDEFGHIJKLMN
1STAFF NOSTAFF NAMEP1P2P1/USTOTAL
2DAYNIGHTDAYNIGHTDAY NIGHT
3LOCAL DATE4/1/2023FO0096THARMA RAJAH00:0100:0200:2000:2000:0200:0000:45
4LOCAL DATE4/1/2023FO0120ABDUL AZIZ B AMRAN00:4000:0700:0000:0900:1000:0001:06
5LOCAL DATE4/1/20230AWANGKU ASIF00:0100:0000:01
6LOCAL DATE5/1/2023FO0096THARMA RAJAH00:0100:0200:2000:2000:0200:0000:45
7LOCAL DATE5/1/2023FO0120ABDUL AZIZ B AMRAN00:4000:0700:0000:0900:1000:0001:06
8LOCAL DATE5/1/20230AWANGKU ASIF00:0100:0000:01
HOUR


VBA Code: 
Sub test()
Dim dict As New Dictionary

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)) Then
    dict.Add A(i, 1) & A(i, 2), i
End If
Next i

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

For Each ss In Range("C12:C" & Cells(12, "C").End(xlDown).Row)
    B = Sheets("Voyage report").Range(Cells(ss.Row, ss.Column), Cells(ss.Row, "n")).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
Next ss

End Sub
 
Upvote 0
RudRud said:
Hi, I might try this again.

Existing names with the same local date will not be copied to hours (sheet), Does it match your criteria? Bcos i still consindering your criteria

Regarding Column O, I can configure double clicking on it so that, for instance, when you click on O12, it will immediately check to see if the staff and local date are already in the hour sheet; if they are not, it will copy the information to the hour sheet. If you click on row 12, will it track row 12 to row 15 or would you prefer to copy each row separately?

vr fixup.xlsm
ABCDEFGHIJKLMN
1STAFF NOSTAFF NAMEP1P2P1/USTOTAL
2DAYNIGHTDAYNIGHTDAY NIGHT
3LOCAL DATE4/1/2023FO0096THARMA RAJAH00:0100:0200:2000:2000:0200:0000:45
4LOCAL DATE4/1/2023FO0120ABDUL AZIZ B AMRAN00:4000:0700:0000:0900:1000:0001:06
5LOCAL DATE4/1/20230AWANGKU ASIF00:0100:0000:01
6LOCAL DATE5/1/2023FO0096THARMA RAJAH00:0100:0200:2000:2000:0200:0000:45
7LOCAL DATE5/1/2023FO0120ABDUL AZIZ B AMRAN00:4000:0700:0000:0900:1000:0001:06
8LOCAL DATE5/1/20230AWANGKU ASIF00:0100:0000:01
HOUR


VBA Code: 
Sub test()
Dim dict As New Dictionary

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)) Then
    dict.Add A(i, 1) & A(i, 2), i
End If
Next i

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

For Each ss In Range("C12:C" & Cells(12, "C").End(xlDown).Row)
    B = Sheets("Voyage report").Range(Cells(ss.Row, ss.Column), Cells(ss.Row, "n")).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
Next ss

End Sub
Click to expand...
hi again!

Existing names with the same local date will not be copied to hours (sheet), Does it match your criteria?<yes is some way. but at the moment from the previous code, i can only copy row 12 and cant make it copy row 13 or 14 if needed.
Regarding Column O, I can configure double clicking on it so that, for instance, when you click on O12, it will immediately check to see if the staff and local date are already in the hour sheet; if they are not, it will copy the information to the hour sheet.(yes and can you do it on row 13, 14 &15, so that, it copy each row if needed.
If you click on row 12, will it track row 12 to row 15 or would you prefer to copy each row separately?<-preferably separetely because sometimes we dont have staff name on row 14 and 15.
i hope this info helps.
 
Upvote 0
Right click sheet voyage report -> View Code - > paste the code below

When you double-click on the columns O12 to O15(If O12 then will copy entire row 12), the code will run and copy each one individually.

vr fixup.xlsm
ABCDEFGHIJKLMN
1STAFF NOSTAFF NAMEP1P2P1/USTOTAL
2DAYNIGHTDAYNIGHTDAY NIGHT
3LOCAL DATE5/1/2023FO0096THARMA RAJAH00:0100:0200:2000:2000:0200:0000:45
4LOCAL DATE5/1/2023FO0120ABDUL AZIZ B AMRAN00:4000:0700:0000:0900:1000:0001:06
5LOCAL DATE5/1/20230AWANGKU ASIF00:0100:0000:01
6LOCAL DATE6/1/2023FO0096THARMA RAJAH00:0100:0200:2000:2000:0200:0000:45
7LOCAL DATE6/1/2023FO0120ABDUL AZIZ B AMRAN00:4000:0700:0000:0900:1000:0001:06
8LOCAL DATE6/1/20230AWANGKU ASIF00:0100:0000:01
HOUR


VBA Code: 
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)) 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
 
Upvote 0
RudRud said:
Right click sheet voyage report -> View Code - > paste the code below

When you double-click on the columns O12 to O15(If O12 then will copy entire row 12), the code will run and copy each one individually.

vr fixup.xlsm
ABCDEFGHIJKLMN
1STAFF NOSTAFF NAMEP1P2P1/USTOTAL
2DAYNIGHTDAYNIGHTDAY NIGHT
3LOCAL DATE5/1/2023FO0096THARMA RAJAH00:0100:0200:2000:2000:0200:0000:45
4LOCAL DATE5/1/2023FO0120ABDUL AZIZ B AMRAN00:4000:0700:0000:0900:1000:0001:06
5LOCAL DATE5/1/20230AWANGKU ASIF00:0100:0000:01
6LOCAL DATE6/1/2023FO0096THARMA RAJAH00:0100:0200:2000:2000:0200:0000:45
7LOCAL DATE6/1/2023FO0120ABDUL AZIZ B AMRAN00:4000:0700:0000:0900:1000:0001:06
8LOCAL DATE6/1/20230AWANGKU ASIF00:0100:0000:01
HOUR


VBA Code: 
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)) 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
Click to expand...
mine got this when i double click on the column O12, highlight on the arrow that i draw
 

Attachments

  • Screenshot 2023-06-05 at 7.28.35 PM.jpg
    Screenshot 2023-06-05 at 7.28.35 PM.jpg
    169.1 KB · Views: 3
Upvote 0
RudRud said:
Right click sheet voyage report -> View Code - > paste the code below

When you double-click on the columns O12 to O15(If O12 then will copy entire row 12), the code will run and copy each one individually.

vr fixup.xlsm
ABCDEFGHIJKLMN
1STAFF NOSTAFF NAMEP1P2P1/USTOTAL
2DAYNIGHTDAYNIGHTDAY NIGHT
3LOCAL DATE5/1/2023FO0096THARMA RAJAH00:0100:0200:2000:2000:0200:0000:45
4LOCAL DATE5/1/2023FO0120ABDUL AZIZ B AMRAN00:4000:0700:0000:0900:1000:0001:06
5LOCAL DATE5/1/20230AWANGKU ASIF00:0100:0000:01
6LOCAL DATE6/1/2023FO0096THARMA RAJAH00:0100:0200:2000:2000:0200:0000:45
7LOCAL DATE6/1/2023FO0120ABDUL AZIZ B AMRAN00:4000:0700:0000:0900:1000:0001:06
8LOCAL DATE6/1/20230AWANGKU ASIF00:0100:0000:01
HOUR


VBA Code: 
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)) 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
Click to expand...
i think i got it to work,and i found out that you must copy it from row12 to 15,only then it will collect data every row, if you copy row 14 then 13 and 12, it will overwrite the value previously what is 13 or 14 for example. is that done purposely? or bug in the coding?like wise, the value is copied as intended just cant be jumble up. One more thing that it can copy existing name with the same date twice.can we add restriction to that or can we overwrite the only existing name that already copied?
i think 98% work perfectly. minor thing need to be address or clarify. thanks a million sir!
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Solved
Copy row 2 and paste it after last row of another worksheet
Replies
9
Views
882
rollis13
rollis13
M
  • Question
Copy values from row to another sheet matched to todays date
Replies
1
Views
296
Herakles
H
J
  • Question
Issues copying data to another sheet
Replies
4
Views
280
jynxy
J
A
  • Question
code to copy last row & exclude zero value of the data in last row for each name
Replies
0
Views
134
Abdo
A
A
  • Question
copy amount before TOTAL row for specific column based on matching names in column with sheet name
Replies
0
Views
142
Alaa mg
A

Forum statistics

Threads
1,215,140
Messages
6,123,269
Members
449,093
Latest member
Vincent Khandagale

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top