VBA move rows once date in past
Results 1 to 6 of 6

Thread: VBA move rows once date in past
Thanks Thanks: 0 Likes Likes: 0

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

    Default VBA move rows once date in past

    Hi I have seen lots of discussion on VBA moving rows to another worksheet based on a value eg "done" which have worked for me. However what I am currently trying to do is move rows based on date, each row has a due date and once this date has passed I would like to move the row to a different worksheet. Any help would be much appreciated. Kind regards Tim

  2. #2
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,730
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: VBA move rows once date in past

    We always need specific details:
    1. What column is the date in? Say column B or G something like that. Do not say column Date
    2. You said move the row. Does that mean copy row to other sheet and delete from orginal sheet?
    3. What is the name of the copy from sheet and the Copy to sheet.
    4.And the script will be activated by pressing a button correct?
    If not how should the script be activated.
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

  3. #3
    New Member
    Join Date
    Jun 2018
    Posts
    6
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: VBA move rows once date in past

    Quote Originally Posted by My Aswer Is This View Post
    We always need specific details:
    1. What column is the date in? Say column B or G something like that. Do not say column Date
    2. You said move the row. Does that mean copy row to other sheet and delete from orginal sheet?
    3. What is the name of the copy from sheet and the Copy to sheet.
    4.And the script will be activated by pressing a button correct?
    If not how should the script be activated.
    Hi, thanks for your response, the additional detail;
    1. Column B
    2. Yes
    3. Copy from "current engagement" copy to "archive engagement"
    4. Yes, activated by pressing a button, I anticipate that everytime I run the script it will move the rows that are now in the past

    Many thanks Tim

  4. #4
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,730
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: VBA move rows once date in past

    Try this:
    Will copy all rows where column B date is less then Today

    Code:
    Sub Filter_Me_Please()
    'Modified  7/30/2019  1:34:32 PM  EDT
    Application.ScreenUpdating = False
    Sheets("current engagement").Activate
    Dim Lastrow As Long
    Dim Lastrowa As Long
    Lastrowa = Sheets("archive engagement").Cells(Rows.Count, "B").End(xlUp).Row + 1
    Dim c As Long
    Dim s As Variant
    c = 2 ' Column Number Modify this to your need
    s = "<" & Date 'Search Value Modify to your need
    Lastrow = Cells(Rows.Count, c).End(xlUp).Row
    With ActiveSheet.Cells(1, c).Resize(Lastrow)
        .AutoFilter 1, s
        counter = .Columns(c).SpecialCells(xlCellTypeVisible).Count
        If counter > 1 Then
            .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Copy Sheets("archive engagement").Rows(Lastrowa)
            .Offset(1).Resize(.Rows.Count - 1).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        
        Else
            MsgBox "No values found"
        End If
        .AutoFilter
    End With
    Application.ScreenUpdating = True
    End Sub
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

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

    Default Re: VBA move rows once date in past

    Hi, thanks for this and apologies for my ignorance, when I try to run this I get runtime error 1004, autofilter method of range class failed and it highlights ".autofilter 1, s

  6. #6
    Board Regular My Aswer Is This's Avatar
    Join Date
    Jul 2014
    Posts
    15,730
    Post Thanks / Like
    Mentioned
    28 Post(s)
    Tagged
    13 Thread(s)

    Default Re: VBA move rows once date in past

    You need to check these sheet names. Look in the script and you will see:

    current engagement

    and:

    archive engagement

    are these your two sheet names?

    If not change the script. These are the names you mentioned in Post 1
    Be sure and always test this script on sample data the first time you use this as to avoid any problems with your data. Always trying to learn more and help others where I can. I'm using Excel 2013.
    Patience please Iím not perfect yet. "Memories are forever"

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
  •