Timestamp on worksheet change

[Sportman1975]

Hi guys,

I have to following problem:
in a workbook with many sheets I have to update several worksheets (Store A,B,C,D,E,F,G,H, etc). But I would like to have a overview sheet (Info) where all worksheets are named with a time stamp when the sheet was updated

Overview sheet is called Info
Field A2:A50 contain the different stores

Store Timestamp
A xxxx
B yyyy
C zzzz
D
E
.
.
.

So what I basically need is a macro which update field B2 in Info-sheet when the specific store is updated.
The current code I have to add in every worksheet but that is probably not the right way. PLease help is required....

Private Sub Worksheet_Change(ByVal Target As Range)
  With Sheets("Info")
    .Range("c2").Value = Environ("username")
    .Range("b2").Value = Now
  End With
End Sub

 

[Yongle]

Try pasting this into the project's ThisWorkbook module

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Application.EnableEvents = False
Sheets("Info").Range("A" & Rows.Count).End(xlUp).Offset(1).Resize(, 3) = Array(Sh.Name, Now, Environ("username"))
Application.EnableEvents = True
End Sub

 

[Sportman1975]

Hi Yongle,
he only changes the field B2 & C2
So the other shops (field A2:A99) from field B3:C99 not….
How to fix this?

 

[Yongle]

Did you copy the code before my edit - I tried to warn you...
Last edited by Yongle; Today at 12:41 PM. Reason: forgot to include Offset oops!

Sheets("Info").Range("A" & Rows.Count).End(xlUp).[COLOR=#ff0000]Offset(1)[/COLOR].Resize(, 3) = Array(Sh.Name, Now, Environ("username"))