date & time stamp

[Trevor3007]

good evening,

i have to time\date stamp every entry placed in a worksheet. Is there a way to use =now() so when a new entry is done , any previous wont update to the new time\date?




MTIA
Trevor 3007

 

[Zenwood]

This code uses now() and the copies and pasts the value all in the same cell.

Sub TimeStamp()


    ActiveCell.FormulaR1C1 = "=NOW()"
    
     ActiveCell.Select
     Selection.Copy
     Selection.PasteSpecial Paste:=xlPasteValues


    Selection.NumberFormat = "dddd, mmmm d \at h:mm:ss  AM/PM"


End Sub

 

[MARK858]

@Zenwood, if you use the VBA version of Now you don't need to paste as values.

Sub TimeStamp()


    ActiveCell = Now
    ActiveCell.NumberFormat = "dddd, mmmm d \at h:mm:ss  AM/PM"


End Sub

 

[Zenwood]

Thanks!

 

[Trevor3007]

@Zenwood, if you use the VBA version of Now you don't need to paste as values.

Sub TimeStamp()


    ActiveCell = Now
    ActiveCell.NumberFormat = "dddd, mmmm d \at h:mm:ss  AM/PM"



End Sub

Mark,

many thanks & very very much appreciated.

I should of included that this is only in a range . this is what I have ATM

=IF(A44>0,NOW(),"") . The formula is I col Y

so can you tweak your VB so that it will only work in column Y

MTIA
KR Trevor 3007

 

[Trevor3007]

Hello Zenwood,

thanks for your help.

& hope Marks tip has helped you too.

Have a great Xmas.

KR
Trevor3007

 

[Trevor3007]

@Zenwood, if you use the VBA version of Now you don't need to paste as values.

Sub TimeStamp()


    ActiveCell = Now
    ActiveCell.NumberFormat = "dddd, mmmm d \at h:mm:ss  AM/PM"


End Sub

hi mark,

thanks again for your help. Am sorry but i dont get it? How does it work? I thought i only had to paste your code where code should go. then place =if(y34>0,now(),"") , format accordingly (ddd\mm\yy hh:mm:ss) enter data in x34 date\time appears....but when i put data in x35 the date\time changes to the same date\time in x34 & x35!!!! aaaaaaaaaaaaaah

please show me the light
KR
Trevor3007

 

[Joe4]

Let's take a step back. We can code this to do what you want and run automatically if you can answer some basic questions:
- What range are these entries going in (is it just one cell on the sheet, or a whole range of cells)?
- How is the cell being updated (manually, copy/paste, or by formula)?
- What range do you want the date stamp placed in (I am guessing some column in the same row as the cell that is being updated)?
- You want the date stamp entered only when the first entry is made (i.e. once the date stamp is populated, it should never be updated, even if the source cell is updated again, right?)

 

[Trevor3007]

Good morning & many thanks Joe4,

A’s to youQ’s as follows:-




Let's takea step back. We can code this to do what you want and run automatically if youcan answer some basic questions:
- What range are these entries going in (is it just one cell on the sheet, or awhole range of cells)?
When data is placed into any cell inthe range X2-X200, the ddd\mm\yyyy hh:mm:ss will then automatically replicateinto the applicable neighboring cell Y2-Y200

- How is the cell being updated (manually, copy/paste, or by formula)?
manually,copy/paste into range X2-X200

- What range do you want the date stamp placed in (I am guessing some column inthe same row as the cell that is being updated)?
Yes , Y2-Y200

- You want the date stamp entered only when the first entry is made (i.e. oncethe date stamp is populated, it should never be updated, even if the sourcecell is updated again, right?)

Only whenthat cell is updated should the date stamp change. If there is no data or an empty cell within the range X2-X200, then the corresponding cell willbe blank ( I was using the formula =IF(A44>0,NOW(),"") for this )

Hoping you can sort.
MTIA
Trevor3007

 

[Joe4]

Go to the sheet that you want to apply this to, right-click on the sheet tab name at the bottom of the screen, select "View Code", and paste this code in the resulting VB Editor window.

Private Sub Worksheet_Change(ByVal Target As Range)

    Dim rng As Range
    Dim cell As Range
    
'   Capture cells updated in our designated range
    Set rng = Intersect(Target, Range("X2:X200"))
    
'   Exit if no cells in range being updated
    If rng Is Nothing Then Exit Sub
    
'   Loop through cells just updated in our designated range
    For Each cell In rng
'       If something in the cell, add date stamp to column Y
        If cell <> "" Then
            cell.Offset(0, 1) = Now()
'       Clear column Y if column X is blank
        Else
            cell.Offset(0, 1).ClearContents
        End If
    Next cell
    
End Sub

I believe this should automatically do what you want.