Newbie Needing Help - Macro will only work in one sheet.

[ijhannah]

Hello,

I'm not familiar at all with using VBA so please bear with me. I am trying to get the below code to work on all the sheets. It works on Sheet 1, but not the rest within the same workbook.

I've read another thread w/ similar question, but the codes are so different I don't even know where to begin. Please help!

All I'm trying to do is have Cell E automatically reflect the current date when an entry is made on Cell D and tabbed over. This must be activated on all 12 sheets in the same workbook.

Thanks in advance.


Private Sub Worksheet_Change(ByVal Target As Excel.Range)
'Updated by Hannah Yu 02/21/2019
Dim xRg As Range, xCell As Range
On Error Resume Next
If (Target.Count = 1) Then
If (Not Application.Intersect(Target, Me.Range("D:D")) Is Nothing) Then _
Target.Offset(0, 1) = Date
Application.EnableEvents = False
Set xRg = Application.Intersect(Target.Dependents, Me.Range("D:D"))
If (Not xRg Is Nothing) Then
For Each xCell In xRg
xCell.Offset(0, 1) = Date
Next
End If
Application.EnableEvents = True
End If
End Sub

 

[DanteAmor]

Remove your macro and put the following code in the Thisworkbook events, this will make it work on all the sheets.
Only the following lines of code are required:

Private Sub Workbook_SheetChange(ByVal Sh As Object, ByVal Target As Range)
    If Target.Count = 1 Then
        If Not Application.Intersect(Target, Range("D:D")) Is Nothing Then
            Target.Offset(0, 1) = Date
        End If
    End If
End Sub

 

[ijhannah]

Sorry - meant to indicate that I'm trying to have column E automatically reflect the current date when an entry is made in a cell in column D.

 

[MARK858]

Sorry - meant to indicate that I'm trying to have column E automatically reflect the current date when an entry is made in a cell in column D.

Is that not what the code DanteAmor posted does

 

[DanteAmor]

Sorry - meant to indicate that I'm trying to have column E automatically reflect the current date when an entry is made in a cell in column D.

To put the code in the events of thisworkbook, go to VBA, in VBAProject press doubleclick in ThisWorkbook, in the panel paste the code.
Go back to excel and on any sheet, in column D enter a data, in automatic in column E you will have the date.

 

[ijhannah]

Thank you, Dante. That worked!!

 

[DanteAmor]

You're welcome & thanks for the feedback.