Jun 03 2023 03:00 AM
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.
Jun 03 2023 04:47 AM
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".
Jun 03 2023 05:49 AM
@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
Jun 03 2023 06:35 AM
Jun 03 2023 07:14 AM
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.
Jun 03 2023 07:33 AM