dcsimg
Results 1 to 15 of 15

Thread: [RESOLVED] time stamp

  1. #1

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Resolved [RESOLVED] time stamp

    Hi,

    I wish to add a time stamp once I've excuted the below macro via a button.

    Can anyone supply the coding for this?

    I wish the time stamp to be uploaded in D13. I've set this as range date_imp_stp.

    This is my coding.

    Sub openAndCopy()
    Dim wbCopy As Workbook
    Dim wsCopy As Worksheet
    Dim rngCopy As Range
    Dim wbPaste As Workbook
    Dim wsPaste As Worksheet
    Dim rngPaste As Range



    Set wbCopy = Workbooks.Open("N:\My Documents\1085.xlsx")
    Set wsCopy = wbCopy.Worksheets("Report1")
    Set rngCopy = wsCopy.Range("A").EntireColumn
    Set wbPaste = Workbooks("Daily Revenue KM.xlsm")
    Set wsPaste = wbPaste.Worksheets("FI Report")
    Set rngPaste = wsPaste.Range("A1")

    rngCopy.Copy
    rngPaste.PasteSpecial

    Application.DisplayAlerts = False


    wbCopy.Close

    Application.DisplayAlerts = True


    Name:  Capture.JPG
Views: 124
Size:  45.4 KB

  2. #2
    Super Moderator si_the_geek's Avatar
    Join Date
    Jul 2002
    Location
    Bristol, UK
    Posts
    41,091

    Re: time stamp

    Welcome to VBForums

    Thread moved from the 'VB.Net' forum to the 'Office Development/VBA' forum.

  3. #3
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,427

    Re: time stamp

    Code:
    Worksheets("Sheet1").Cells(4, 13) = Now
    Replace Sheet1 with the appropriate sheet name where you want cell D13 updated.

  4. #4

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Re: time stamp

    Hi jdc2000,

    I tried the above and it didn't work. The time did not update.

    I also tried

    wbPaste.Worksheet("Control").Cells(4, 13) = Now

    Do I need to define the function somehow?

  5. #5

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Question Re: time stamp

    Hi jdc2000,

    I tried the above and it didn't work. The time did not update.

    I also tried

    wbPaste.Worksheet("Control").Cells(4, 13) = Now

    Do I need to define the function somehow?

  6. #6
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,444

    Re: time stamp

    Do I need to define the function somehow?
    that should work to put the date and time into the specified cell, you can also format the date/time, either in the worksheet cell format, or vb format
    did you get any error or other indication of a problem?

    BUT if you put the timestamp into a cell before pasting it could possibly be overwritten by the pasted data, depending on the area of the data, though according to the code the date would be in a different sheet t the pasted data
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  7. #7
    Frenzied Member jdc2000's Avatar
    Join Date
    Oct 2001
    Location
    Idaho Falls, Idaho USA
    Posts
    1,427

    Re: time stamp

    Did you check the cell format for that cell? It may be set to only show the date.

    Try this:

    Code:
    wbPaste.Worksheet("Control").Cells(4, 13) = Now
    Worksheet("Control").Cells(4, 13).NumberFormat = "dd-mm-yyyy hh:mm:ss"

  8. #8

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Re: time stamp

    Thanks for the above JDC2000

    It worked. however it only worked for that day and since then the time stamp has not updated. Do you know why that would happen?

  9. #9

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Re: time stamp

    Thanks for the above JDC2000

    It worked. however it only worked for that day and since then the time stamp has not updated. Do you know why that would happen?

  10. #10
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,444

    Re: time stamp

    you did not indicate you wanted the time to update

    how often do you want update to happen? when you open the workbook? change some value? every second?

    maybe post your full current code and specify what changes you want
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  11. #11

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Re: time stamp

    Here's my code.

    I want the time stamp to update every time I run this code.

    Sub openAndCopy()
    Dim wbCopy As Workbook
    Dim wsCopy As Worksheet
    Dim rngCopy As Range
    Dim wbPaste As Workbook
    Dim wsPaste As Worksheet
    Dim rngPaste As Range



    Set wbCopy = Workbooks.Open("N:\My Documents\1085.xlsx")
    Set wsCopy = wbCopy.Worksheets("Report1")
    Set rngCopy = wsCopy.Range("A").EntireColumn
    Set wbPaste = Workbooks("Daily Revenue KM.xlsm")
    Set wsPaste = wbPaste.Worksheets("FI Report")
    Set rngPaste = wsPaste.Range("A1")

    rngCopy.Copy
    rngPaste.PasteSpecial

    Application.DisplayAlerts = False


    wbPaste.Worksheet("Control").Cells(4, 13) = Now

    wbCopy.Close

  12. #12
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,444

    Re: time stamp

    i do not see any reason why the cell would not update the time when ever the code is run

    of course it will only update when the smallest time interval is increased, possibly every minute
    if you need more frequent updating try
    Code:
    wbPaste.Worksheet("Control").cells(4,13) = format(now,"dd/mm/yy hh:nn:ss")
    which should update every second
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  13. #13

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Re: time stamp

    Getting a run time error 424

    Object required.

  14. #14
    PowerPoster
    Join Date
    Dec 2004
    Posts
    24,444

    Re: time stamp

    indicates wbpaste is not assigned, it should work in place of your original code
    i do my best to test code works before i post it, but sometimes am unable to do so for some reason, and usually say so if this is the case.
    Note code snippets posted are just that and do not include error handling that is required in real world applications, but avoid On Error Resume Next

    dim all variables as required as often i have done so elsewhere in my code but only posted the relevant part

    come back and mark your original post as resolved if your problem is fixed
    pete

  15. #15

    Thread Starter
    Member
    Join Date
    May 2019
    Posts
    58

    Re: time stamp

    Yep all good now. Thank you.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Featured


Click Here to Expand Forum to Full Width