Here's my dilemma...

I have 37 different worksheets (one for each major principal in my company)
that are to be updated on a semi-regular basis. I would like for each of
these worksheets to have its own time/date stamp that automatically updates
itself to the current date and time whenever a change is made on the
worksheet. The time/date stamp on one sheet must be independent of the
time/date stamps on all other sheets. In other words, when the time and date
is updated on one sheet none of the other time/date stamps on the other
sheets change unless they are modified as well. I have tried using
SendKeys...I thought I had something, but it seemed too complicated to get it
to work. And I've also tried using the Worksheet_Change event...I've been
tinkering with this code:

Private Sub Worksheet_Change(ByVal Target As Range)

'THIS SUB UPDATES THE DATE/TIME STAMP WHENEVER A CHANGE IS MADE TO A
PRINCIPAL'S SHEET
'Update "Date" cell
Dim Cell As Range
For Each Cell In Target
With Cell
If .Column = Range("B:B").Column Then
Cells(.Row, "F").Value = Int(now)
End If
End With
Next Cell

End Sub

Can someone please advise? Thanks!

Thanks for responding!

Each sheet is labled with the last name of each principal in alpabetical
order. Let's look at the first four (for the sake of simplicity): Brunner,
Doak, Gilbert, Gillaspie

When something is changed on Doak's sheet, then the time/date on that sheet
ONLY should update. None of the others should change. What can you tell me
about this?

"Rick Rothstein" wrote:

I think this worksheet code will do what you want...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
If Target.Column = 2 Then Target.Offset(0, 4).Value = Now
End Sub

Put the code in the ThisWorkbook code window, **not** in any Worksheet's
code window (and you can remove you attempted code from each worksheet's
code module as the above code (if placed in the ThisWorkbook code window)
will handle all sheets in the workbook. If you have other worksheets in the
workbook, then you will need to use the Sh argument to test if the code
should be run or not (I would need to see your sheet names, or at least the
principal's worksheets naming scheme, in order to show you some filtering
code).

--
Rick (MVP - Excel)


"RVS" wrote in message
...
Here's my dilemma...

I have 37 different worksheets (one for each major principal in my
company)
that are to be updated on a semi-regular basis. I would like for each of
these worksheets to have its own time/date stamp that automatically
updates
itself to the current date and time whenever a change is made on the
worksheet. The time/date stamp on one sheet must be independent of the
time/date stamps on all other sheets. In other words, when the time and
date
is updated on one sheet none of the other time/date stamps on the other
sheets change unless they are modified as well. I have tried using
SendKeys...I thought I had something, but it seemed too complicated to get
it
to work. And I've also tried using the Worksheet_Change event...I've been
tinkering with this code:

Private Sub Worksheet_Change(ByVal Target As Range)

'THIS SUB UPDATES THE DATE/TIME STAMP WHENEVER A CHANGE IS MADE TO A
PRINCIPAL'S SHEET
'Update "Date" cell
Dim Cell As Range
For Each Cell In Target
With Cell
If .Column = Range("B:B").Column Then
Cells(.Row, "F").Value = Int(now)
End If
End With
Next Cell

End Sub

Can someone please advise? Thanks!

The code I gave will do what you want (change something on a worksheet and
the date will be placed on that line on that worksheet only)... however,
what I was trying to tell you is that, as written, the code will do that for
**all** worksheets in the workbook. So, if you have other worksheets besides
those for your principals (a summary sheet for example), those would get
dates placed on them also whenever a physical change is made to them. This
is what I meant when I said you might have to filter on the Sh argument in
order to make the code apply only to your principal's worksheets.

--
Rick (MVP - Excel)


"RVS" wrote in message
...
Thanks for responding!

Each sheet is labled with the last name of each principal in alpabetical
order. Let's look at the first four (for the sake of simplicity): Brunner,
Doak, Gilbert, Gillaspie

When something is changed on Doak's sheet, then the time/date on that
sheet
ONLY should update. None of the others should change. What can you tell me
about this?

"Rick Rothstein" wrote:

I think this worksheet code will do what you want...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Target.Column = 2 Then Target.Offset(0, 4).Value = Now
End Sub

Put the code in the ThisWorkbook code window, **not** in any Worksheet's
code window (and you can remove you attempted code from each worksheet's
code module as the above code (if placed in the ThisWorkbook code window)
will handle all sheets in the workbook. If you have other worksheets in
the
workbook, then you will need to use the Sh argument to test if the code
should be run or not (I would need to see your sheet names, or at least
the
principal's worksheets naming scheme, in order to show you some filtering
code).

--
Rick (MVP - Excel)


"RVS" wrote in message
...
Here's my dilemma...

I have 37 different worksheets (one for each major principal in my
company)
that are to be updated on a semi-regular basis. I would like for each
of
these worksheets to have its own time/date stamp that automatically
updates
itself to the current date and time whenever a change is made on the
worksheet. The time/date stamp on one sheet must be independent of the
time/date stamps on all other sheets. In other words, when the time and
date
is updated on one sheet none of the other time/date stamps on the other
sheets change unless they are modified as well. I have tried using
SendKeys...I thought I had something, but it seemed too complicated to
get
it
to work. And I've also tried using the Worksheet_Change event...I've
been
tinkering with this code:

Private Sub Worksheet_Change(ByVal Target As Range)

'THIS SUB UPDATES THE DATE/TIME STAMP WHENEVER A CHANGE IS MADE TO A
PRINCIPAL'S SHEET
'Update "Date" cell
Dim Cell As Range
For Each Cell In Target
With Cell
If .Column = Range("B:B").Column Then
Cells(.Row, "F").Value = Int(now)
End If
End With
Next Cell

End Sub

Can someone please advise? Thanks!

I got it! Thanks for your help!

"Rick Rothstein" wrote:

The code I gave will do what you want (change something on a worksheet and
the date will be placed on that line on that worksheet only)... however,
what I was trying to tell you is that, as written, the code will do that for
**all** worksheets in the workbook. So, if you have other worksheets besides
those for your principals (a summary sheet for example), those would get
dates placed on them also whenever a physical change is made to them. This
is what I meant when I said you might have to filter on the Sh argument in
order to make the code apply only to your principal's worksheets.

--
Rick (MVP - Excel)


"RVS" wrote in message
...
Thanks for responding!

Each sheet is labled with the last name of each principal in alpabetical
order. Let's look at the first four (for the sake of simplicity): Brunner,
Doak, Gilbert, Gillaspie

When something is changed on Doak's sheet, then the time/date on that
sheet
ONLY should update. None of the others should change. What can you tell me
about this?

"Rick Rothstein" wrote:

I think this worksheet code will do what you want...

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As
Range)
If Target.Column = 2 Then Target.Offset(0, 4).Value = Now
End Sub

Put the code in the ThisWorkbook code window, **not** in any Worksheet's
code window (and you can remove you attempted code from each worksheet's
code module as the above code (if placed in the ThisWorkbook code window)
will handle all sheets in the workbook. If you have other worksheets in
the
workbook, then you will need to use the Sh argument to test if the code
should be run or not (I would need to see your sheet names, or at least
the
principal's worksheets naming scheme, in order to show you some filtering
code).

--
Rick (MVP - Excel)


"RVS" wrote in message
...
Here's my dilemma...

I have 37 different worksheets (one for each major principal in my
company)
that are to be updated on a semi-regular basis. I would like for each
of
these worksheets to have its own time/date stamp that automatically
updates
itself to the current date and time whenever a change is made on the
worksheet. The time/date stamp on one sheet must be independent of the
time/date stamps on all other sheets. In other words, when the time and
date
is updated on one sheet none of the other time/date stamps on the other
sheets change unless they are modified as well. I have tried using
SendKeys...I thought I had something, but it seemed too complicated to
get
it
to work. And I've also tried using the Worksheet_Change event...I've
been
tinkering with this code:

Private Sub Worksheet_Change(ByVal Target As Range)

'THIS SUB UPDATES THE DATE/TIME STAMP WHENEVER A CHANGE IS MADE TO A
PRINCIPAL'S SHEET
'Update "Date" cell
Dim Cell As Range
For Each Cell In Target
With Cell
If .Column = Range("B:B").Column Then
Cells(.Row, "F").Value = Int(now)
End If
End With
Next Cell

End Sub

Can someone please advise? Thanks!