Results 1 to 7 of 7

Thread: VBA to select rows before today's date and paste as values
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    New Member
    Join Date
    Dec 2018
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default VBA to select rows before today's date and paste as values

    I have a workbook that is used daily to keep track of new data coming in each day. The "Summary" sheet uses formulas determine the day and then pull relevant data from other sheets in the workbook to track historical data, going all the way back to 2016. To prevent the formulas from slowing down the workbook, every few weeks I go into the historical sheet and paste as values all rows before tomorrow's date, so that only future dates remain as formula.

    Instead of doing this, I would like to be able to use a script that will search either 1) Column A to find today's date or 2) find the last row between columns A and L that contains data (not just formula), and then copy and paste that row (between columns A and L) as values over itself.

    If possible I would also like a script similar to this, but that would delete all rows (minus the header) on the "History" sheet that contain a date before today's date in column BW.

    For reference, on both sheets new days are added at the bottom, with the earliest dates starting in Row 2.

  2. #2
    Board Regular
    Join Date
    Jan 2015
    Posts
    1,121
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to select rows before today's date and paste as values

    This is to go through column a, which sheet and to do what?
    Code:
      '1)search Column A to find today's date
            Dim LookCell As Range
            'On which sheet? Please adapt Sheet1 in line below
             Dim sh As Worksheet: Set sh = Worksheets("Sheet1")
            'Last Row column A
             Dim lr As Long: lr = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
             For Each LookCell In Range("A1:A" & lr)
                If LookCell = DateSerial(Year:=Year(Now), Month:=Month(Now), Day:=Day(Now)) Then
                'what you want to do with this cell/row? Put the code here below
                
                End If
             Next LookCell

  3. #3
    Board Regular
    Join Date
    Jan 2015
    Posts
    1,121
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to select rows before today's date and paste as values

    I assume worksheet is same than before, so this one works with sh
    Code:
    '2) find the last row between columns A and L that contains data (not just formula)
             Dim lrAL As Long
             lrAL = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
             Dim i As Long
             For i = 2 To 12
                If sh.Cells(sh.Rows.Count, i).End(xlUp).Row > lrAL Then
                   lrAL = sh.Cells(sh.Rows.Count, i).End(xlUp).Row
                End If
             Next i
             ' copy and paste that row (between columns A and L) as values over itself.
                Range("A" & lrAL & ":L" & lrAL).Copy
                Range("A" & lrAL & ":L" & lrAL).PasteSpecial Paste:=xlPasteValues
    Last edited by Kamolga; Dec 18th, 2018 at 03:38 PM.

  4. #4
    Board Regular
    Join Date
    Jan 2015
    Posts
    1,121
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to select rows before today's date and paste as values

    Code:
     '3) delete all rows on the "History" sheet that contain a date before today's date in column BW.
             'Last row Bw
             Dim ws As Worksheet: Set ws = Worksheets("History")
             Dim lrBW As Long: lrBW = ws.Cells(ws.Rows.Count, "BW").End(xlUp).Row
             ws.Select
             Dim dCell As Range
             For Each dCell In ws.Range("BW2:BW" & lrBW)
                If (dCell.Value < DateSerial(Year:=Year(Now), Month:=Month(Now), Day:=Day(Now)) And dCell <> "") Then
                   dCell.EntireRow.Delete
                End If
             Next dCell
    Last edited by Kamolga; Dec 18th, 2018 at 03:53 PM.

  5. #5
    New Member
    Join Date
    Dec 2018
    Posts
    2
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to select rows before today's date and paste as values

    The deletion vba works great, but there seems to be an issue with steps 1 and 2. For some reason the script doesn't paste over the row with today's date (in this case that would be row 513), but instead seems to paste a blank row in row 659. In case it matters, each time the workbook is opened on a new day, a new row will appear (there are formulas down to row 658, so tomorrow row A514 will be read 12/19/18 instead of being blank).

  6. #6
    Board Regular
    Join Date
    Jan 2015
    Posts
    1,121
    Post Thanks / Like
    Mentioned
    8 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to select rows before today's date and paste as values

    I am not sure to understand what you try to do. The code below mixes both: It looks on a sheet called History (you can change it) for the last cell containing today's date in its A column. When it finds it, it selects A to L of that row and copy-paste their values. Hope this is what you are looking for

    Code:
    '1)search Column A to find last occurence of today's date
            'On sheet called History
             Dim sh As Worksheet: Set sh = Worksheets("History")
            'Last Row column A
             Dim lr As Long: lr = sh.Cells(sh.Rows.Count, "A").End(xlUp).Row
            'Today's date
             Dim tDate As Date: tDate = DateSerial(Year:=Year(Now), Month:=Month(Now), Day:=Day(Now))
            'Last occururence of today value in Column A, the cell is called dCell
                Dim dcell As Range: Set dcell = sh.Range("A1:A" & lr).Find(tDate, , , , , searchdirection:=xlPrevious)
            'Copy-paste value of last occurence row (column A to L)
            Range("A" & dcell.Row & ":L" & dcell.Row).Copy
            Range("A" & dcell.Row & ":L" & dcell.Row).PasteSpecial Paste:=xlPasteValues
    Last edited by Kamolga; Dec 19th, 2018 at 05:34 AM.

  7. #7
    Board Regular
    Join Date
    Jan 2014
    Location
    Dublin, Ireland
    Posts
    1,421
    Post Thanks / Like
    Mentioned
    1 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA to select rows before today's date and paste as values

    firstly, "History" is a reserved name so not sure how you can have a sheet named this?

    secondly, are column A's data formulas or constants? if the formulas are dragged down past the point of the meaningful data Kamolga's last row code will give the incorrect row number
    Learn something new everyday.

    be sure to use code tags

    Code:
    [ code ]
    [ / code ]
    ' no spaces

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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