Automatically move and delete based on date

[Mievo]

Hello,

I have the following code that is moving candidates if I select "Yes" (move candidates) in "M:M".

Now I would like to integrate the following: As soon as I open the Excel it should check if the date in $D is older than today's date. If this is the case (to the contract ended), I want it automatically to move it to "Ehemalige Mitarbeiter" like in the example-code and delete the entire row afterwords out of the "Mitarbeiter"-sheet.

How can I realize this?

Thank you in advance.

[Sintek]

If this does not work upload a sample file please...

Sub Auto_Open()
With Sheets("Mitarbeiter").Cells(1).CurrentRegion
    .AutoFilter 4, "<" & CLng(Date)
    .Offset(1).SpecialCells(12).Copy Sheets("Ehemalige Mitarbeiter").Range("A" & Rows.Count).End(xlUp).Offset(1)
    .Offset(1).SpecialCells(12).EntireRow.Delete
    .AutoFilter
End With
End Sub

[Mievo]

Cheers,

where to put it? Into the module or in the worksheet-code?

Thank you in advance

[Sintek]

Std Module...

[Mievo]

It's not working 100%. I created an example file. Please note that the names are from a name generator, all info's are fictive. I added in red and bold what I want to achieve within the "Mitarbeiter"-worksheet, so it is easier for you probably.

Thank you in advance for your help and effort, it is much appreciated!

[Sintek]

Why don't users ever start working in A1 on a worksheet?

Sub Auto_Open()
With Sheets("Mitarbeiter").Cells(5, 2).CurrentRegion
    .AutoFilter 3, "<" & CLng(Date)
    .Offset(1).Resize(.Rows.Count - 1).Columns("B:I").Copy Sheets("Ehemalige Mitarbeiter").Range("B" & Rows.Count).End(xlUp).Offset(1)
    .Offset(1).SpecialCells(12).EntireRow.Delete
    .AutoFilter
End With
End Sub

[Mievo]

Thank you. Could you test my sheet? I get "runtimeerror 13" (= original error: "Laufzeitfehler 13") when opening the Excel, but I think the error is in my worksheet-code I added to "Tabelle9 (Mitarbeiter)"..

Edit: And your code moved all employees to the sheet, even though their contract didn't end yet. And it moved it into "A", but it must be moved from "B" on.

[Sintek]

Upload file with expected result on sheet...because this is your requirement...

As soon as I open the Excel it should check if the date in $D is older than today's date. If this is the case (to the contract ended), I want it automatically to move it to "Ehemalige Mitarbeiter" like in the example-code and delete the entire row afterwords out of the "Mitarbeiter"-sheet.

even though their contract didn't end yet

Does this not mean Vertragsende which is in Col E?

Sub Auto_Open()
With Sheets("Mitarbeiter")
    With .Range("B5:M" & .Cells(.Rows.Count, 2).End(xlUp).Row)
        .AutoFilter 4, "<" & CLng(Date)
        .Offset(1).SpecialCells(12).Copy Sheets("Ehemalige Mitarbeiter").Range("B" & Rows.Count).End(xlUp).Offset(1)
        .Offset(1).SpecialCells(12).EntireRow.Delete
        .AutoFilter
    End With
End With
End Sub

And why oh why do you have 1,048,576 rows populated with data validation?

[Mievo]

Yes, you were right, it was E, not D. My bad, sorry.

Here is the Excel sheet. It is not working 100%. It moves people, but 1. it doesn't set back the filter, so the data is still filtered and 2. (which is more important) it doesn't move every candidate. I have one candidate with April/2020 in there and she didn't get moved to the "Ehemalige Mitarbeiter" sheet, even though the contract ended months ago. Not sure why this is happening because it is moving other candidates aswell.

Thanks for checking!

[Sintek]

This is the last time...
Every upload you keep on changing...
April only has 30 days that's why it is not copying and deleting...Your date 31/04.2020
It is resetting the filter...
Your dates are not actual dates...
See sample it works...