Copy row from one worksheet to another based on date (Expired Date)

[mandii01]

Hi All,

I've been reading quite a bit and I'm not sure which is the best way to approach this whether there is an excel formula that can achieve what I want or if it will be a vba macro.

What I have: A "PROGRAM" spreadsheet that shows names of employees with their high risk work licences and when the licences are expiring.

What I need: To transfer rows that have an expired date to another sheet within the workbook - so that I can get a compressed list of who has expired licences.

So if I have John Smith with any "expired" date against his licence class his Surname, first name, licence number, expiry date and licence class would be transferred into the "EXPIRED" worksheet.

I also have conditional formatting applied.

Sorry I would post an example of the workbook but I can't seem to find the "attach" icon.

Please Help - any guidance is greatly appreciated.

Thanks again.

Mandii

[TABLE="width: 964"]
<colgroup><col span="2"><col><col span="3"><col span="2"></colgroup><tbody>[TR]
[TD]SURNAME[/TD]
[TD]FIRST NAME[/TD]
[TD]LICENCE NUMBER[/TD]
[TD="colspan: 2"]FORKLIFT TRUCK [/TD]
[TD="colspan: 2"] SCAFFOLDING[/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]LF[/TD]
[TD] LO[/TD]
[TD] SB[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]SMITH[/TD]
[TD]John[/TD]
[TD]1234567[/TD]
[TD="align: right"]28/05/2016[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]BLOGGS[/TD]
[TD]Joe[/TD]
[TD]0003060041[/TD]
[TD="align: right"]13/01/2020[/TD]
[TD="align: right"] 13/01/2020[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]WHO[/TD]
[TD]Damien[/TD]
[TD]TBA[/TD]
[TD="align: right"]18/05/2021[/TD]
[TD="align: right"]11/08/2016[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]SMITHE[/TD]
[TD]Taylor[/TD]
[TD]0003306329[/TD]
[TD="align: right"]20/01/2020[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD]DOE[/TD]
[TD]Jane[/TD]
[TD]0003176316[/TD]
[TD="align: right"]11/05/2017[/TD]
[TD] [/TD]
[TD="align: right"]15/07/2016[/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
[TR]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[/TR]
</tbody>[/TABLE]

 

[My Aswer Is This]

Please give the column number we need to do a search in for the expiration date.
Say like search column (12) for expired date.
Then tell us what columns you want copied over to the sheet named "EXPIRED"

Say like Columns "1" to columns "10" copy to sheet "EXPIRED"

 

[mandii01]

[TABLE="class: grid, width: 500, align: center"]
<tbody>[TR]
[TD]ROW[/TD]
[TD]Column B[/TD]
[TD]Column C[/TD]
[TD]Column D[/TD]
[TD]Column E[/TD]
[TD]Column F[/TD]
[TD]Column G[/TD]
[TD]Column H[/TD]
[TD]Column I[/TD]
[TD]Column J[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]Surname[/TD]
[TD]First Name[/TD]
[TD]Licence Number[/TD]
[TD]WP Licence[/TD]
[TD]CN Licence[/TD]
[TD]DG Licence[/TD]
[TD]LF Licence[/TD]
[TD]LO Licence[/TD]
[TD]SB Licence[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]SMITH[/TD]
[TD]John[/TD]
[TD]1234567[/TD]
[TD]15/10/2016[/TD]
[TD]30/10/2016[/TD]
[TD][/TD]
[TD]28/05/2016[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]BLOGGS[/TD]
[TD]Joe[/TD]
[TD]0001112223[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]13/01/2020[/TD]
[TD]13/01/2020[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]WHO[/TD]
[TD]Damien[/TD]
[TD]TBA[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]18/05/2021[/TD]
[TD]11/08/2016[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]DOE[/TD]
[TD]Jane[/TD]
[TD]9998887776[/TD]
[TD][/TD]
[TD][/TD]
[TD]15/10/2019[/TD]
[TD]11/05/2019[/TD]
[TD][/TD]
[TD]15/07/2016[/TD]
[/TR]
</tbody>[/TABLE]

Note:

• Row & Columns are the automated "headings" in excel.
• I have conditional formatting to highlight cells for when they are within 30 days of expiry date, 14 days of expiry date, 7 days and then due and expired.
• I would like the second "Expired" sheet just showing the expired row details i.e. in this example those personnel on the expired sheet would be John Smith, Damien Who and Jane Doe.
• The layout of the second "Expired" sheet would mirror the original sheet however I only want the details of those that have expired dates listed.
• Search cells for "Expired" dates would need to be from Column E to column J
• Copy of whole row i.e. column B - J Row 7 into second "Expired" spreadsheet.

Thanks all in advance, any help and guidance is greatly appreciated. I am thinking that there won't be an excel formula and that this may require a Vba macro.

Cheers,

Amanda

 

[My Aswer Is This]

Edit

 

[My Aswer Is This]

Try this:

If any date in columns 5 to 10 are less then today that columns data will be copied to a sheet named Expired"
Run this script from the sheet with your data.

Sub License_Check()
Application.ScreenUpdating = False
Dim i As Long
Dim Lastrow As Long
Dim Lastrowa As Long
Lastrow = Cells(Rows.Count, "A").End(xlUp).Row
Lastrowa = Sheets("Expired").Cells(Rows.Count, "A").End(xlUp).Row + 1
    For i = 3 To Lastrow
        If Cells(i, 5) < Date Or Cells(i, 6) < Date Or Cells(i, 7) < Date Or Cells(i, 8) < Date Or Cells(i, 9) < Date Or Cells(i, 10) < Date Then
            Rows(i).Copy Destination:=Sheets("Expired").Rows(Lastrowa)
            Lastrowa = Lastrowa + 1
        End If
    Next
Application.ScreenUpdating = True
End Sub