Date Issue

[klpw]

Hi all,

I have issue with my excel vba. I would like to create excel sheet in which when there is new data entry in sheet1, it will automatically copy and paste into Sheet2 and show the date. For tomorrow, the date will disappear for yesterday's new data and show tomorrow's date when new data entry being copied in Sheet2. Please see attached for illustration and below for my code.

In Module 1:

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

 

[NARAYANK991]

Hi ,

Instead of posting your code , and uploading a screenshot , can you not upload your entire workbook , with the code and data in it ?

Secondly , what is the problem you are facing with your code as it stands at present ?

Narayan

 

[klpw]

Hi Narayan,

I'm facing the error that the uploaded file is too large and i don't know why. It's just 5.36MB. The issue I have now is that when the data is being copied and pasted in Sheet2, the column B doesn't show today's date.

Cheers

 

[NARAYANK991]

Hi ,

The maximum file size allowed for uploads in this forum is 1 MB ; if your file is bigger , you can upload it on any public file-sharing website such as DropBox , SkyDrive , and post the download link here in this thread.

Based on your clarification , let me see if I can spot the problem.

Narayan