klpw
01-20-2016, 10:43 PM
Hi guys,
I'm currently having issue with my Excel vba code. When new data entry is added in Sheet1, it will show new on column B and will automatically copy and paste data in Sheet2 with today's date. In my case, it doesn't show today's date for new entries in Sheet2. Also, tomorrow I want to clear all the New for yesterday and show New on column B for any added data entry tomorrow in Sheet1 and yesterday's date become all clear and show today's date for new data entry in Sheet2. May I know how can I change my code to achieve this as shown in the picture? My code is as below:
In Module1:
Sub Run()
Dim lastRow As Long
Dim y As Worksheet
Dim t As Worksheet
'lastRow = Sheets("y").Range("A100000").End(xlUp).Row + 1 ' then next free row in sheet2
Sheets("Sheet1").Range("C:V").Copy Destination:=Sheets("Sheet2").Range("C:V")
End Sub
Public Sub CreationDate(ByRef Target As Range)
Const CreateColumn As String = "B"
With Target
With .Worksheet.Cells(.Row, CreateColumn)
If Not IsDate(.Value) Then .Value = Date
.Offset(0, 1).Select
End With
End With
End Sub
In Sheet1
Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range, B As Range, Inte As Range, r As Range, myvalue As String
Set C = Range("C:C")
Set Inte = Intersect(C, Target)
myvalue = "New"
If Inte Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each r In Inte
r.Offset(0, -1).Value = myvalue
Next r
Application.EnableEvents = True
Sheets("Sheet1").Range("C:V").Copy Destination:=Sheets("Sheet2").Range("C:V")
End Sub
In Sheet2
Private Sub Worksheet_Change(ByVal Target As Range)
Const TriggerColumn As String = "C"
Const HeaderRows As Long = 1
With Target
If .Column = Asc(TriggerColumn) - 64 Then
If .Row > HeaderRows And _
Trim(.Value) <> vbNullString Then _
CreationDate Target
End If
End With
End Sub
In ThisWorkbook
Private Sub Workbook_Open()
Sheets("Sheet1").Range("A1").Value = Format(Date, ("dd/mm/yyyy"))
Sheets("Sheet2").Range("A1").Value = Date - 1
If Sheets("Sheet1").Range("B:B").Text = "New" Then
Sheets("Sheet2").Range("B:B").Value = Date
Save
End If
End Sub
I'm currently having issue with my Excel vba code. When new data entry is added in Sheet1, it will show new on column B and will automatically copy and paste data in Sheet2 with today's date. In my case, it doesn't show today's date for new entries in Sheet2. Also, tomorrow I want to clear all the New for yesterday and show New on column B for any added data entry tomorrow in Sheet1 and yesterday's date become all clear and show today's date for new data entry in Sheet2. May I know how can I change my code to achieve this as shown in the picture? My code is as below:
In Module1:
Sub Run()
Dim lastRow As Long
Dim y As Worksheet
Dim t As Worksheet
'lastRow = Sheets("y").Range("A100000").End(xlUp).Row + 1 ' then next free row in sheet2
Sheets("Sheet1").Range("C:V").Copy Destination:=Sheets("Sheet2").Range("C:V")
End Sub
Public Sub CreationDate(ByRef Target As Range)
Const CreateColumn As String = "B"
With Target
With .Worksheet.Cells(.Row, CreateColumn)
If Not IsDate(.Value) Then .Value = Date
.Offset(0, 1).Select
End With
End With
End Sub
In Sheet1
Private Sub Worksheet_Change(ByVal Target As Range)
Dim C As Range, B As Range, Inte As Range, r As Range, myvalue As String
Set C = Range("C:C")
Set Inte = Intersect(C, Target)
myvalue = "New"
If Inte Is Nothing Then Exit Sub
Application.EnableEvents = False
For Each r In Inte
r.Offset(0, -1).Value = myvalue
Next r
Application.EnableEvents = True
Sheets("Sheet1").Range("C:V").Copy Destination:=Sheets("Sheet2").Range("C:V")
End Sub
In Sheet2
Private Sub Worksheet_Change(ByVal Target As Range)
Const TriggerColumn As String = "C"
Const HeaderRows As Long = 1
With Target
If .Column = Asc(TriggerColumn) - 64 Then
If .Row > HeaderRows And _
Trim(.Value) <> vbNullString Then _
CreationDate Target
End If
End With
End Sub
In ThisWorkbook
Private Sub Workbook_Open()
Sheets("Sheet1").Range("A1").Value = Format(Date, ("dd/mm/yyyy"))
Sheets("Sheet2").Range("A1").Value = Date - 1
If Sheets("Sheet1").Range("B:B").Text = "New" Then
Sheets("Sheet2").Range("B:B").Value = Date
Save
End If
End Sub