Date and time stamp in different sheet

[aliaslamy2k]

I need two VBA codes to record date and time in Sheet2 if any cell is changed or updated in sheet 1 for two different scenario

Scenario 1. Record date and time in Sheet2 if any cell is changed or updated in Sheet1 between A5 : AP100
Scenario 2. Record date and time in Sheet2 if any cells is changed/updated between F2:F100, H2:H100, I2:I100, L2:L100 in sheet1

In both above scenario, the date and time stamp should be in the same cell of Sheet2 as it is in Sheet1.

Example:

Cell F2 in Sheet 1 is updated, then date and time should capture in Cell F2 of Sheet 2.

Thank you

 

[My Aswer Is This]

Some of your ranges are duplicate.
Like A5 : AP100
And
F2:F100
Some of those ranges are in the same Range.
Do you really need to have some start in row 2 and others in row 5?

 

[aliaslamy2k]

Hi,

I have two different workbooks with two different scenarios. so there will be no duplicates.
Each VBA will go in each workbook.

I can start from row 5 in both workbook.

Scenario 1. Record date and time in Sheet2 if any cell is changed or updated in Sheet1 between A5 : AP100
Scenario 2. Record date and time in Sheet2 if any cells is changed/updated between F5:F100, H5:H100, I5:I100, L5:L100 in sheet1

 

[My Aswer Is This]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
The script will run when you manually change any value in the range

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  3/4/2020  1:41:37 PM  EST
If Not Intersect(Target, Range("A5 : AP100")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As String
ans = Target.Address
Sheets(2).Range(ans).Value = Now
End If
End Sub

 

[aliaslamy2k]

Try this:
This is an auto sheet event script
Your Workbook must be Macro enabled
To install this code:
Right-click on the sheet tab
Select View Code from the pop-up context menu
Paste the code in the VBA edit window
The script will run when you manually change any value in the range

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified  3/4/2020  1:41:37 PM  EST
If Not Intersect(Target, Range("A5 : AP100")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As String
ans = Target.Address
Sheets(2).Range(ans).Value = Now
End If
End Sub

Hiii,

This code works very well. Thank you so much.

Is there any way to add username as well, as i want to know who updated this sheet.

Thank you once again

 

[My Aswer Is This]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 3/5/2020 12:54:09 AM EST
If Not Intersect(Target, Range("A5 : AP100")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As String
ans = Target.Address
Sheets(2).Range(ans).Value = Now & "  " & Application.UserName
End If
End Sub

 

[aliaslamy2k]

Try this:

Private Sub Worksheet_Change(ByVal Target As Range)
'Modified 3/5/2020 12:54:09 AM EST
If Not Intersect(Target, Range("A5 : AP100")) Is Nothing Then
If Target.Cells.CountLarge > 1 Or IsEmpty(Target) Then Exit Sub
Dim ans As String
ans = Target.Address
Sheets(2).Range(ans).Value = Now & "  " & Application.UserName
End If
End Sub

Its working very well !
Thank you so much once again

 

[My Aswer Is This]

Its working very well !
Thank you so much once again

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

 

[aliaslamy2k]

Glad I was able to help you.
Come back here to Mr. Excel next time you need additional assistance.

Yes Sure, Mr. Excel has become a life saver for me. I have saved thousands of hours with the help of Mr. Excel.
Great platform !!

Thank you once again