Expired data copy to another sheet. But expired data repeat when i run it.

[jp16]

Hi, my company has a Data tab where it has all the contract. It has all the expiry date. As and when, the admin will manually copy and paste those expired contract to another sheet call "Expiry". I am thinking of writing a VBA which will Copy and paste those Expired Contract to the "Expiry" tab. Hence i write the code whenever the "Expiry" tab is activated.

Please Login or Register  to view this content.

I realise that the Expired data will repeat whenever i select Expiry tab. Hence making it useless... Is there a way to solve it? i know i can solve the problem by adding a Clear Content on top. But i believe there is a better way to do it. Please advice.... =)

[xladept]

Try this:

Please Login or Register  to view this content.

[jp16]

Hi, it seems to work. But how come it include those > today??

[xladept]

On the expiry tab you're only getting previous dates What do you want

[jp16]

Ya, the expiry tab should show onli expired date. I copy n paste ur code, but those "after today" is being Copy n paste too.

[xladept]

That's not happening on my system - have you extracted your code??

[jp16]

Hi, see my file...
Maybe its setting issue???

[jp16]

Hey, weird. I tried to with date like 01/01/2020. It wont copy and paste.
Hence i key in 01/01/2018 to 31/12/2018. Then weird, it will copy till 03/10/2018. I aso duno y it will stop at that date.
If onli i can make a video to show u.... see the excel for "proof"

[xladept]

Try this, it clears the expiry sheet first - looks like it was just replacing the pertinent rows, so when you had the entire data on the expiry, it was working but made effectively no change in the display

Please Login or Register  to view this content.

[xladept]

But 01/01/2018 to 31/12/2018 are all previous dates, is there another criterion for the filter?

[jp16]

I think u misunderstood. I mean i wan the code to Copy and Paste only Date BEFORE today to Expiry Tab. Hence, if my data has 01/01/2018 to 31/12/2018, it should copy 01/01/2018 to 09/04/2018 to the expiry tab if i run the Code. N tomorrow, if i run the code, it should Copy and Paste 10/04/2018. If i run the code again on 15/04/2018, it should copy n paste 11/04/2018 to 14/04/2018.
But now, it copy 01/01/2018 to 03/10/2018 to expiry tab. i duno y it will copy till that date too.

[xladept]

I mean i wan the code to Copy and Paste only Date BEFORE today to Expiry Tab

But, isn't that what it does