Agnarr
New Member
- Joined
- Jan 15, 2023
- Messages
- 29
- Office Version
- 365
- Platform
- Windows
Hello everybody and you all do an amazing work.
I need your help please and i apologize in advance for any mistakes (English is not my native language).
So, I have made the following:
In B column appears a timestamp when in column C somone inputs a code which automatically reaplaces it with a given name and in column D is just for notes.
E.g.: in c1 i ender 3232 and press enter. Automatically 3232 turns to "3232 - Mr. Smith" and in b1 appears "29/01 12:05".
My problem is that I want to copy and paste entries alongside the length of the sheet but keeping the same timestamp. Now every time I copy-paste entries, even if i copy the already given date, the VBA automatically changes the timestamp to the moment i paste the entry.
The Code i use it the following:
Thank you all in advance.
I need your help please and i apologize in advance for any mistakes (English is not my native language).
So, I have made the following:
In B column appears a timestamp when in column C somone inputs a code which automatically reaplaces it with a given name and in column D is just for notes.
E.g.: in c1 i ender 3232 and press enter. Automatically 3232 turns to "3232 - Mr. Smith" and in b1 appears "29/01 12:05".
My problem is that I want to copy and paste entries alongside the length of the sheet but keeping the same timestamp. Now every time I copy-paste entries, even if i copy the already given date, the VBA automatically changes the timestamp to the moment i paste the entry.
The Code i use it the following:
VBA Code:
Private Sub Worksheet_Change(ByVal Target As Range)
Dim inputR As Range, codeR As Range, cell As Range, WRng As Range, rg As Range, zOffsetColumn As Integer, f
Set inputR = Range("C14:C208")
Set codeR = Worksheets("ÊÙÄ.ÐÅË.").Range("A1:B300")
Set WRng = Intersect(Application.ActiveSheet.Range("c14:c208"), Target)
zOffsetColumn = -1
If Not WRng Is Nothing Then
Application.EnableEvents = False
For Each rg In WRng
If Not VBA.IsEmpty(rg.Value) Then
rg.Offset(0, zOffsetColumn).Value = Now
rg.Offset(0, zOffsetColumn).NumberFormat = "dd/mm, hh:mm"
Else
rg.Offset(0, zOffsetColumn).ClearContents
End If
Next
Application.EnableEvents = True
End If
If Target.CountLarge > 1 Then Exit Sub
If Intersect(Target, inputR) Is Nothing Then Exit Sub
With Application
.EnableEvents = False
For Each cell In Target
Set f = codeR.Find(cell.Value, , , xlWhole)
If Not f Is Nothing Then cell.Value = f.Offset(, 1).Value
Next
.EnableEvents = True
End With
End Sub
Thank you all in advance.