VBA Cut rows with date range

[Littlemalky]

I'm using Excel 2007 with data in columns A:H on a workbook entitled, "Fullfillment - Domestic.xlsx", on the worksheet entitled, "Materials". The columns will remain static; however, the amount of data below will change every week. I've sorted column D in descending order. Column D contains dates. I basically need VBA to cut the sequential rows that contain dates from the current month, the previous month, and all dates beyond the current month (everything in the future).

The days are not my focus when cutting the data, the actual month is my only guide (i.e. today is March 15th, I need to cut everything in February, and everything beyond March). This all needs to be relative to the month we are in as I run this report weekly so I can't just call upon March everytime.

The dates in this column are formatted as such: "m/dd/yyyy".

This cut needs to be pasted to another sheet entitled, "New Releases". Then I need the rows deleted from which we cut on the "Materials" worksheet. Is this possible?

 

[Weaver]

This works in the UK. You might have to tinker with the date formatting for other regions.

Sub deleteOtherMonths()
    dt1 = Format(DateSerial(Year(Date), Month(Date), 1), "dd mmm yyyy")
    dt2 = Format(DateSerial(Year(Date), Month(Date) + 1, 1), "dd mmm yyyy")
    Application.ScreenUpdating = 0
    With Sheets("materials")
        Set lr = .Cells(Rows.Count, "A").End(xlUp)
        With .Range("A1").CurrentRegion
            .AutoFilter
            .AutoFilter Field:=1, Criteria1:="<" & dt1, Operator:=xlOr, Criteria2:=">=" & dt2
        End With
        On Error Resume Next
        .Range("A2", lr).EntireRow.SpecialCells(xlCellTypeVisible).EntireRow.Delete
        On Error GoTo 0
        .Range("A1").CurrentRegion.AutoFilter
    End With
End Sub

PS it also assumes your headers are on row 1 - you ought to be able to alter it without much fuss.

HTH

PPS sorry, just noticed I misread your query, re the pasting of the data to another sheet.

I think you could run this then add a bit of code to paste the remaining lines to the right location.