Results 1 to 11 of 11
  1. #1
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Auto Date command? (2000)

    Hello,

    Is there any in Excel of inserting a date into a document when you open it?

    Regards
    Justin

  2. #2
    New Lounger
    Join Date
    Apr 2004
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Date command? (2000)

    =today()

    This will put in todays date everytime you open it

  3. #3
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Date command? (2000)

    That's great is there any way that l call up this command when l click on a button?

    Thanks
    Justin

  4. #4
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Auto Date command? (2000)

    Ctrl + ;

    will add the current date (unchanging). If you want to add the formula to the selected cell(s) with a button, add this macro to a button:

    <pre>Sub TodaySelection()
    Selection.Formula = "=today()"
    End Sub</pre>


    Then highlight the cells and run the macro.

    Steve

  5. #5
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Date command? (2000)

    I tried this but got an error.

    See attachment.

    Justin.

  6. #6
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Auto Date command? (2000)

    <P ID="edit" class=small>(Edited by sdckapr on 06-Apr-04 09:03. Added addtional comments)</P>Is the file named 'IncomingPCMs.xls" open?
    Does that file have a macro named Macro4?

    Also, if it is not open, does excel know its location so it can open it?
    If you assigned it to a button, where does the button thing the macro is located (macro name, file name, pathname, drive letter)?


    Steve

  7. #7
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Date command? (2000)

    This appears to work fine.
    I have now created a macro to output the date and some text but it only works on 1 row.
    Any ideas as to what l am doing wrong?
    If l press on the macro it works fine for the first time but only displays the date when l press on it a second time in another row. The text in columns 4,5, & 6 is not displayed for some odd reason.


    see attachment.

    Justin

  8. #8
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Auto Date command? (2000)

    Not without seeing your macro. Could you post the code, and tell us what you expect or want it to do, and what it does or does not do as the case may be.

    <img src=/S/whisper.gif border=0 alt=whisper width=29 height=17> it is better to attach a file rather than screen shot as it gives us more info. A screen shot is best when you want to "capture" an error message, not for giving us information.

    Steve

  9. #9
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Date command? (2000)

    What l would like the macro to do is to output the date in the 1st column and words "Submission..." in the 4th Column and the words "Gail" in the 5th and "Mr.." in the 6th,

    Justin
    -------


    '
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Range("E466:G466").Select
    Selection.Copy
    Range("E467:G467").Select
    ActiveSheet.Paste
    Range("I467").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    End Sub
    Sub Incoming_pcm()
    '
    ' Incoming_pcm Macro
    ' Macro recorded 06/04/2004 by jjr
    '

    '
    Range("E466:G466").Select
    Selection.Copy
    Range("E467:G467").Select
    ActiveSheet.Paste
    Range("I467").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    End Sub
    Sub Macro11()
    '
    ' Macro11 Macro
    ' Macro recorded 06/04/2004 by jjr
    '

    '
    ActiveCell.FormulaR1C1 = "=TODAY()"
    Range("E467:G467").Select
    Selection.Copy
    Range("E468:G468").Select
    ActiveSheet.Paste
    Range("I468").Select
    Application.CutCopyMode = False
    Selection.ClearContents
    End Sub

  10. #10
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: Auto Date command? (2000)

    I don't understand exactly why you need the macro. It seems like you could do it by copy and paste.

    This will take the items from cos 4,5,6 of row 466 (change as desired) and put it into cols 4,5,6 of the rows that are selected. Also col 1 of the rows you select will have the formula = today()


    Steve

    <pre>Option Explicit
    Sub jj1234Macro()
    Dim rng As Range
    Dim lRow As Long
    Dim lSourceRow As Long
    Dim x As Integer

    lSourceRow = 466

    Set rng = Selection.EntireRow
    For lRow = 1 To rng.Rows.Count
    rng.Cells(lRow, 1).Formula = "=Today()"
    For x = 4 To 6
    rng.Cells(lRow, x) = Cells(lSourceRow, x)
    Next
    Next
    End Sub</pre>


  11. #11
    5 Star Lounger
    Join Date
    Mar 2004
    Posts
    924
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Auto Date command? (2000)

    Good point by using copy and paste.
    Altough l require the macro as the previous lines of text might not be the same as the line of text l require on the macro.
    Using the macro will save me an awful lot of time in the long run.

    Thanks for your help.

    Justin.

Posting Permissions

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