Timestamp without Worksheet_Change

[sassriverrat]

Hello-

Trying to figure out how to do a timestamp that can be embedded within a macro (so I don't have to copy it to every sheet). Here's the coding I was running as a Worksheet_Change

thanks

Private Sub Worksheet_Change(ByVal Target As Range)    With Application
        .EnableEvents = False
        .ScreenUpdating = False
    End If
    
    If Not Intersect(Target, Range("R8,W25")) Is Nothing Then
        
        If Cells(8, 18) <> "" And Cells(25, 23) = "" Then
            Cells(4, 6) = Date
            Cells(4, 6).NumberFormat = "dd-mmm-yyy"
        End If


        If Cells(25, 23) <> "" Then
            Cells(4, 6) = Cells(25, 23).Value
            Cells(4, 6).NumberFormat = "dd-mmm-yyy"
        End If
        
          If Cells(8, 18) = "" And Cells(25, 23) = "" Then
            Cells(4, 6) = "No Data Input"
        End If



        With Application
            .EnableEvents = True
            .ScreenUpdating = True
        End With


    End If
End Sub

 

[ravishankar]

try the following codes

Sub sassri()
For a = 1 To Sheets.count
    Sheets(a).Cells(4, 6).NumberFormat = "dd-mmm-yyy"
    If Sheets(a).Cells(8, 18) <> "" Then
        If Sheets(a).Cells(25, 23) <> "" Then
        Sheets(a).Cells(4, 6) = Cells(25, 23).Value
        Else
        Sheets(a).Cells(4, 6) = Date
        End If
    Sheets(a).Cells(4, 6) = "No data found"
    End If
    MsgBox "updated"
End Sub

run the macro on a copy of your workbook and ensure it is working the way you want.
ravishankar

 

[James006]

Hello,

Do not forget you can always use events at the WorkBook level ... which will take care of all your sheets ... with your single macro ...:wink:

 

[sassriverrat]

So thoughts here gentlemen- and sorry for the time delay here-
@ravishankar - I added a "next" right before the msgbox to satisfy the code and allow the macro to run. However, it didn't give me any results...not sure why though. I set my code to call your macro at the end of my formatting macro.
@James006 - As an alternative idea to Ravishankar, so I probably did it wrong but i changed my code to start with a "Sub Workbook_Change(ByVal Target As Range)" and it didn't work.

Additionally, I'd like this to work for Noon, Noon2, Noon3, etc (infinitely) as well as the "Arrival" sheet. I have a few other sheets in the workbook (like "Notes") that it needs to not affect.

Thanks Guys!

 

[James006]

Hello,

If you want to dig further and understand Workbook Event Macros ...

https://www.ozgrid.com/Excel/free-training/ExcelVBA1/excelvba1lesson15.htm

HTH

 

[ravishankar]

add "Next a" after last end if and try it again
ravishankar