Transfer Data depending on date that is older than from the current month

[JACK JOUSH]

Hello everyone , I hope someone can help with this
I am looking to create a macro that upon running, will look at the dates in column H and if this dates is before older than from the current month
or any other words Are recorded manually In the same column , Transfer this rows to archives sheet. With the replacement of the date condition to the word "finished " Then deleted of rows that were moved from the main sheet .... If possible I need flexible way as original data is too large... please check the attached file
Any help would be greatly appreciated!!! - thanks in advance...

[nankw83]

Hi again JACK JOUSH

Try below code ...

Please Login or Register  to view this content.

[JACK JOUSH]

That's wonderful .. I am so grateful for you Mr. nankw
What about replacing the date condition with the word finished?

[nankw83]

So in “ archives” sheet column H should always have the word “finished” ?

[nankw83]

If my understanding is correct, try below code ...

Please Login or Register  to view this content.

[JACK JOUSH]

I'm sure you understand me well
not always ... if condition is met , The date condition should be replaced with the word "finished"
As for any other words, they are recorded manually And it is transferred To the archive sheet as it is
Thank you for your cooperation in advance

[nankw83]

Ok, text should remain as is & dates to be converted to "finished"

Please Login or Register  to view this content.

[JACK JOUSH]

Thank you A son of Kuwait, it works perfectly
I'll come back later after testing the speed code on the real file.
Best Regards from the deep of my heart

[JACK JOUSH]

Welcome Mr. nankw
There is a simple problem , The data should be filtered until the end of each month.
For testing record this date 30/4/2021 and note that this date .... It is not transferred To the archive sheet ,Thanks again.

[Marc L]

According to the attachment a VBA demonstration as a starter :

Sub Demo1() 
       Dim V, L& 
    With Sheet1.[A1].CurrentRegion.Rows 
           V = .Parent.Evaluate(Replace("IF({1},(H2:H#<=EOMONTH(TODAY(),-1))+ISTEXT(H2:H#))", "#", .Count)) 
           L = Application.Sum(V) 
        If L Then 
                Application.ScreenUpdating = False 
               .Range("I2:I" & .Count).Value2 = V 
               .Resize(, 9).Sort .Range("I1"), 1, Header:=xlYes 
               .Columns(9).Clear 
          With .Item(.Count + 1 - L & ":" & .Count).Columns 
               .Item(8).Value2 = .Parent.Evaluate(Replace("IF(ISTEXT(#),#,""Finished"")", "#", .Item(8).Address)) 
               .Cut Sheet2.Cells(Rows.Count, 1).End(xlUp)(2) 
          End With 
                Application.ScreenUpdating = True 
        End If 
    End With 
End Sub  

► Do you like it ? ► ► So thanks to click on bottom left star icon « ★ Add Reputation » ! ◄ ◄

[nankw83]

There is a simple problem , The data should be filtered until the end of each month.
For testing record this date 30/4/2021 and note that this date .... It is not transferred To the archive sheet

Do you mean 30/4/2021 date should be transferred to "archives" sheet ? If yes, then you only need to add = in the code as highlighted below, if this is not what you mean, please post a sample file

Please Login or Register  to view this content.

[JACK JOUSH]

Thanks nankw and Marc L
In fact all solutions offered are very awesome
Once again, thank you for you help.

[nankw83]

Glad to help