Timestamp on worksheet change

[nparsons75]

Hi,

I have two worksheets

1. sheet 1
2. sheet 2

In sheet 1 I a have two cells that I would like to autofil with username and timestamp when data is changed on sheet 2.

A1 = username
A2 = timestamp


If anything changes on sheet 2 I need A1 and A2 to have the username and timestamp automatically added. The aim is to see when sheet 2 was last updated.

Thank you in advance.

 

[Peter_SSs]

1. Right click the 'Sheet 2' name tab and choose "View Code".
2. Copy and Paste the code below into the main right hand pane that opens at step 1.
3. Close the Visual Basic window & test.
4. Your workbook will need to be saved as a macro-enabled workbook (*.xlsm).

Private Sub Worksheet_Change(ByVal Target As Range)
  With Sheets("Sheet 1")
    .Range("A1").Value = Environ("username")
    .Range("A2").Value = Now
  End With
End Sub

 

[nparsons75]

Thank you so much for the detailed help. The username works perfectly however the date stamp is not. Any ideas? Thanks again for all your help.

 

[Peter_SSs]

The username works perfectly however the date stamp is not. Any ideas?

In what way is it not working? Just saying that it is "not working", doesn't give much to go on.

 

[nparsons75]

Hi, my apologies. However it it working now. By not working I meant the value was not appearing in A2. After resterting Excel it is, my error. Thanks again for the help, its working great now.

 

[MrRosco]

Hi,

is it possible to do this for multiple rows, on my workbook which is the sales pipeline this would be good for when opportunities are won or lost.

Thanks in advance for your help

Ross

 

[Peter_SSs]

is it possible to do this for multiple rows, ..

Yes it is, but to suggest an actual code, we would need to know details of your sheets. That is, names, where the data is that has to be monitored, where the user & timestamp should go etc. Remember we have never seen your sheet and know nothing about your 'sales pipeline' or what you you mean by 'opportunities won or lost'.

 

[MrRosco]

Yes it is, but to suggest an actual code, we would need to know details of your sheets. That is, names, where the data is that has to be monitored, where the user & timestamp should go etc. Remember we have never seen your sheet and know nothing about your 'sales pipeline' or what you you mean by 'opportunities won or lost'.

Hi Peter,

Apologies for not being clear enough,

on my pipeline, i would like a time stamp next to the opportunity status, so if Won or Lost is selected and active left blank. the status is in column V Row 4 to an undetermined amount. i would preferably like the stamps to be in columns Y,Z.

the sheet name is Pipeline2018

if you need anything else please let me know

 

[Peter_SSs]

if you need anything else please let me know

1. What column(s) do 'Won' or 'Lost' appear in?

2. Are Won or Lost entered manually or are they the result of a formula?

3. What column is 'Active'?

4. Is the Active column entered manually or is it the result of a formula?

5. If you haven't already clarified with the above answers,
a) what goes in column V (from row 4 down) and
b) is it entered manually or the result of a formula?

 

[MrRosco]

1. What column(s) do 'Won' or 'Lost' appear in? -V

2. Are Won or Lost entered manually or are they the result of a formula? - Drop down selection Active/Won/Lost

3. What column is 'Active'? - (Active is one of the three selections in col V)

4. Is the Active column entered manually or is it the result of a formula? - (Drop down)

5. If you haven't already clarified with the above answers,
a) what goes in column V (from row 4 down) and
b) is it entered manually or the result of a formula?

- (These will all be drop down selections)

If you need anything else please let me know

Rosco