VBA macro to transfer data based on date

[RockstarJim]

Hello all,

I have data for projects on a sheet named Master with several target dates. What I am wanting to do is transfer all data within that row to another sheet named Past Due, when the date in column F surpasses "todays" date. I would like this to happen automatically at midnight, but if that is not possible I could do it using a commend button.

 

[peter2047]

Sounds like this should be run from the Windows Task Scheduler.
1. Setup a PowerShell script to run Excel macro
2. Create a .bat file to run the PowerShell script
3. Create a basic task in the Windows Task Scheduler to run the .bat to run at the specific time

 

[RockstarJim]

That's a little more involved than what I need.

 

[RockstarJim]

So this is what I got so far, but will not work with the correct date. column F holds the target date, and if that target date has passed "today's" date, it should copy the complete row to the past_due sheet.

Private Sub CommandButton1_Click()


Dim rng As Range
Application.ScreenUpdating = False
Dim copySheet As Worksheet
Dim pasteSheet As Worksheet


Set pasteSheet = Worksheets("Past_Due")
Set rng = Range("A4").CurrentRegion
Set rng = rng.Offset(1, 0)


If Sheets(Master).Cells(F, Column).Value <= today Then
Sheets(Past_Due).Rows(F).EntireRow.Hidden = False
End If


rng.Copy
pasteSheet.Cells(Rows.Count, 1).End(xlUp).Offset(1, 0).PasteSpecial xlPasteValues
Application.CutCopyMode = False
Application.ScreenUpdating = True


End Sub