+ Reply to Thread
Results 1 to 8 of 8

Using auto open to append recent/current dates to existing dated table rows

  1. #1
    Forum Contributor
    Join Date
    05-25-2019
    Location
    Sioux Falls, South Dakota
    MS-Off Ver
    Microsoft 365
    Posts
    178

    Using auto open to append recent/current dates to existing dated table rows

    Earlier, I posted a request for a macro that would automatically add on dates to the last existing date in an Excel table's C column when the sheet or workbook is opened. However, I failed to specify that all of the previous dated rows needed to stay and that new rows should be appended to the existing rows preceding the new dated rows. I have, as of now, the following two macros generously offered by Mumps1 and Fluff13, do a nice job of adding on the missing dates following 7/10/20 up to today's date of 7/19/20.

    Please Login or Register  to view this content.
    Please Login or Register  to view this content.
    However, the preceding dates of 7/1/20 through 7/9/20 also get wiped out and replaced with these dates, and interestingly, leaves the D and E column information in the same row number location... Not exactly what I wanted. It was an oversight on my part not to clarify that the preceding rows needed to be left in place and that new rows with the newer dates should be appended to them. So, to further clarify, the C column should have dates starting with 7/1/20 in row #2, 7/2/20 in row #3, 7/3/20 in row #4,... all the way to today's (or the most current) date of 7/19/20... not just having 7/10/20 in the worksheet row #2 and ending with 7/19/20 in row #11.

    The goal is to allow the user of this form to not have to enter the date value and therefore ensure that dates are entered in consecutive order.

    Please help. Attached is the workbook.
    Attached Files Attached Files

  2. #2
    Forum Expert Logit's Avatar
    Join Date
    12-23-2012
    Location
    North Carolina
    MS-Off Ver
    Excel 2019 Professional Plus - 2007 Enterprise
    Posts
    6,986

    Re: Using auto open to append recent/current dates to existing dated table rows

    .
    What would row #12 look like if the user opened the workbook ?

  3. #3
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Using auto open to append recent/current dates to existing dated table rows

    Try this :

    PHP Code: 
    Private Sub Workbook_Open()
        
    Dim lLastrow As LongoldDate As DatenewDate As Date
        With Sheets
    ("Phase")
            
    lLastrow = .Range("C" & .Rows.Count).End(xlUp).Row
            oldDate 
    = .Range("C" lLastrow).Value
            newDate 
    Date
            
    Do While oldDate newDate
                oldDate 
    oldDate 1
                lLastrow 
    lLastrow 1
                
    .Range("C" lLastrow).Value oldDate
            Loop
        End With
    End Sub 

  4. #4
    Forum Contributor
    Join Date
    05-25-2019
    Location
    Sioux Falls, South Dakota
    MS-Off Ver
    Microsoft 365
    Posts
    178

    Re: Using auto open to append recent/current dates to existing dated table rows

    Hi Logit,
    Row #12 should have # 11 in column A, Saturday in Column B, 7/11/20 in column C, etc. Row 20 should have 7/19/20 in column C, after the macro has run its course.

  5. #5
    Forum Contributor
    Join Date
    05-25-2019
    Location
    Sioux Falls, South Dakota
    MS-Off Ver
    Microsoft 365
    Posts
    178

    Re: Using auto open to append recent/current dates to existing dated table rows

    Sorry Phuocam, that macro doesn't work.
    Tried putting code in Module too. Nothing happened.

  6. #6
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Using auto open to append recent/current dates to existing dated table rows

    Quote Originally Posted by Dbroek View Post
    Tried putting code in Module too. Nothing happened.
    Hi;

    The code is in Thisworkbook, not the Module.
    Attached Files Attached Files

  7. #7
    Forum Contributor
    Join Date
    05-25-2019
    Location
    Sioux Falls, South Dakota
    MS-Off Ver
    Microsoft 365
    Posts
    178

    Re: Using auto open to append recent/current dates to existing dated table rows

    That works beautifully. I tried it several times with new workbooks, worked every time.
    Thanks so much!

  8. #8
    Forum Guru
    Join Date
    02-27-2016
    Location
    Vietnam
    MS-Off Ver
    2016
    Posts
    5,891

    Re: Using auto open to append recent/current dates to existing dated table rows

    You're welcome.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. [SOLVED] Auto enter current and preceding dates to table column when sheet opens
    By Dbroek in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-17-2020, 03:01 PM
  2. Replies: 2
    Last Post: 09-10-2019, 10:42 PM
  3. VBA to APPEND rows to Existing Excel Worksheet
    By ikslohap in forum Access Tables & Databases
    Replies: 1
    Last Post: 12-14-2015, 06:29 PM
  4. Remove duplicates - leaving most recent dated line
    By Nikki Fox in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 02-04-2015, 06:36 AM
  5. Macro to open closed file append new rows, and update existing rows
    By capson in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 11-06-2014, 11:12 AM
  6. [SOLVED] vba to open recent dated file in a folder
    By prabhuduraraj09 in forum Excel Programming / VBA / Macros
    Replies: 12
    Last Post: 05-10-2014, 08:47 AM
  7. Open multiple dated text files and import dated data specified in file name
    By andygxxxv in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 01-21-2013, 03:56 PM

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1