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

SURNAME	FIRST NAME	LICENCE NUMBER	FORKLIFT TRUCK	SCAFFOLDING
LF	LO	SB
SMITH	John	1234567	28/05/2016
BLOGGS	Joe	0003060041	13/01/2020	13/01/2020
WHO	Damien	TBA	18/05/2021	11/08/2016
SMITHE	Taylor	0003306329	20/01/2020
DOE	Jane	0003176316	11/05/2017		15/07/2016

<colgroup><col span="2"><col><col span="3"><col span="2"></colgroup><tbody>
</tbody>

 

[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]

ROW	Column B	Column C	Column D	Column E	Column F	Column G	Column H	Column I	Column J
3	Surname	First Name	Licence Number	WP Licence	CN Licence	DG Licence	LF Licence	LO Licence	SB Licence
4	SMITH	John	1234567	15/10/2016	30/10/2016		28/05/2016
5	BLOGGS	Joe	0001112223				13/01/2020	13/01/2020
6	WHO	Damien	TBA				18/05/2021	11/08/2016
7	DOE	Jane	9998887776			15/10/2019	11/05/2019		15/07/2016

<tbody>
</tbody>

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