VBA to copy entire row and paste in another sheet if condition is met

[aliaslamy2k]

Dear Experts,

I am looking for a VBA to copy entire row from ( Sheet Name: System Report) if date is less than 25 Jun 2023 and paste in another sheet (Sheet name: Exp Passport)
Also if same VBA can highlight the entire row in yellow in (Sheet Name: System Report) would be great.

COPY FROM:

Designation	Family Name/Last name	First Given Name	Second Given Name	Contact	PPT No	Passport Expiry Date
aa1	bb1	cc1	dd1	ee1	AB123456	18/12/2022
aa2	bb2	cc2	dd2	ee2	AB123457	29/11/2028
aa3	bb3	cc3	dd3	ee3	AB123458	17/07/2022
aa4	bb4	cc4	dd4	ee4	AB123459	24/08/2025
aa5	bb5	cc5	dd5	ee5	AB123460	26/03/2029
aa6	bb6	cc6	dd6	ee6	AB123461	31/10/2020
aa7	bb7	cc7	dd7	ee7	AB123462	4/2/2022
aa8	bb8	cc8	dd8	ee8	AB123463	14/03/2023
aa9	bb9	cc9	dd9	ee9	AB123464	13/04/2028
aa10	bb10	cc10	dd10	ee10	AB123465	22/12/2022
aa11	bb11	cc11	dd11	ee11	AB123466	16/12/2023
aa12	bb12	cc12	dd12	ee12	AB123467	11/3/2019
aa13	bb13	cc13	dd13	ee13	AB123468	25/06/2024
aa14	bb14	cc14	dd14	ee14	AB123469	17/12/2024
aa15	bb15	cc15	dd15	ee15	AB123470	23/04/2029
aa16	bb16	cc16	dd16	ee16	AB123471	24/04/2019
aa17	bb17	cc17	dd17	ee17	AB123472	13/05/2025
aa18	bb18	cc18	dd18	ee18	AB123473	25/02/2031
aa19	bb19	cc19	dd19	ee19	AB123474	14/12/2022
aa20	bb20	cc20	dd20	ee20	AB123475	13/01/2024
aa21	bb21	cc21	dd21	ee21	AB123476	28/07/2031
aa22	bb22	cc22	dd22	ee22	AB123477	21/02/2025
aa23	bb23	cc23	dd23	ee23	AB123478	04/02/2025
aa24	bb24	cc24	dd24	ee24	AB123479	18/06/2015
aa25	bb25	cc25	dd25	ee25	AB123480	24/05/2032

PASTE HERE

Designation	Family Name/Last name	First Given Name	Second Given Name	Contact	PPT No	Passport Expiry Date

Thank You so much in advance.

 

[Joe4]

I don't know that VBA is really necessary here. This can be accomplished quite easily without it.

For the copying/pasting, you could simply use Advanced Filters, which allows you do filter data and copy it to another location (sheet).
Simply filter on your date field.
See: How to copy data to another worksheet with Advanced Filter in Excel?
Note at the bottom of that link, they also show you how to apply this with VBA code, if you really want that.

And for your second part, highlighting the copied rows in yellow, you can simply use Conditional Formatting to do that.
Simply highlight all the rows meeting your date criteria.

Note: Your date column looks a little funny. The fact that you have some date right-justified and some left-justified seems to suggest that you have a mixture of valid date entries, and dates entered as text. You will need to fix that up, as the "dates entered as text" will not be treated as dates and not work right.