Didn't cater for an empty sheet.

Try this better version

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
Const MASTER_SHEET As String = "Master" '<=== change to suit
Dim wsMaster As Worksheet
Dim cRows As Long
Dim iRow As Long

Set wsMaster = Worksheets(MASTER_SHEET)
If Sh.Name < wsMaster.Name Then
cRows = wsMaster.Cells(wsMaster.Rows.Count, "A").End(xlUp).Row
If cRows = wsMaster.Rows.Count Then
wsMaster.Cells(1, "A").Value = "Sheet"
wsMaster.Cells(1, "B").Value = "UserName"
wsMaster.Cells(1, "C").Value = "Timestamp"
wsMaster.Cells(1, "D").Value = "Cell"
wsMaster.Cells(1, "E").Value = "Value"
wsMaster.Rows(1).Font.Bold = True
iRow = 2
Else
iRow = cRows + 1
End If
wsMaster.Cells(iRow, "A").Value = Sh.Name
wsMaster.Cells(iRow, "B").Value = Environ("UserName")
wsMaster.Cells(iRow, "C").Value = Format(Now, "dd-mmm-yyyy
hh:mm:ss")
wsMaster.Cells(iRow, "D").Value = Target.Address(False, False)
If Target.HasFormula Then
wsMaster.Cells(iRow, "E").Value = "'" & Target.Formula
Else
wsMaster.Cells(iRow, "E").Value = Target.Text
End If
wsMaster.Columns("A:E").AutoFormat
End If

End Sub






--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Rajat" wrote in message
...
Dear Bob

thanx for ur effort, but i faced few problems and would try to clarify it,

i've copied the code but it gave me a 'Run Time Error 9'
I am not an expet in VBA, as mentioned by you to modify the "MAster" i've
put the Print area of the worksheet $A$1:$Y$74 the same run time error i
got
and the line
"Set wsmaster = Worksheets(MASTER_SHEET)" was yellow marked

then i changed the "MAster" with the tab names (i have 55 sheets those are
named 1, 2, 3 ... to 55) '1'!:'55'! but same error occured.

my requirement is i want to know when last data entry was made in each
sheet
i.e. 1, 2, 3, etc that time and Date to be displayed in that perticular
sheet's specific cell i.e. for worksheet named '1' Time shown in Cell
'1'!$A$75 and Date shown in Cell '1'!$A$76.

am i able to explain you the problem Bob, hope taht you may sort it out
quickly.