VBA move rows once date in past

Tshurlock

New Member
Joined
Jun 28, 2018
Messages
7
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
 

Excel Facts

Create a Pivot Table on a Map
If your data has zip codes, postal codes, or city names, select the data and use Insert, 3D Map. (Found to right of chart icons).
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.
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,551
Messages
6,114,266
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top