VBA - Delete Rows based on Date criteria from second sheet and Paste in New Rows from third sheet

[banneduser123]

i'm new to macros but I'm decent at understanding so have gotten a fair amount of simple stuff done recently. mostly off amending recorded macros.

let me explain my file. i don't know how to add spaces or borders in here and i just spent like 10 minutes googling that. anyway, there are 3 columns: Report Date, Section, and CUSIP.


Sheet 2
Report Date | Section | CUSIP
-----------------------------------
8/30/2019 | US Sales | XYZ
8/30/2019 | US Sales | ABC
8/30/2019 | Foreign Sales | 123
8/30/2019 | Foreign Sales | 456
8/30/2019 | Foreign Sales | 789
8/30/2019 | Foreign Sales | 1011
7/30/2019 | Foreign Sales | 1213
7/30/2019 | US Sales | EFG
7/30/2019 | US Sales | HIJ
7/30/2019 | US Sales | KLM
7/30/2019 | US Sales | NO14
7/30/2019 | Foreign Sales | 1516P

In cell A1 of Sheet_1, there is a Date i want to reference to. For this example, let's say Sheet_1 A1 = 9/30/2019.

Now, i need the macro to look at the data in Sheet_2 Column A and delete every single row that has a date older than my reference Date by at least two months.

So, if my reference date in Sheet_1 A1 is 9/30/2019, then I need every line deleted on Sheet_2 that has the date of 7/29/2019 or before that.

There are thousands of rows of data. So, what would be the most efficient way of doing this?

Also, each month's data will likely be blocked together in groups...meaning i won't have alternating rows of one month to another.

Essentially, this is just removing data that is 2 months old from the sheet.

After it's done with that, i need the macro to go to Sheet_3, and copy the data from row 2, until the last row that has data in it. next, it then pastes values into the first blank row of data in Sheet_2.

Hopefully that is clear. Basically, in other words, I have a sheet that has two months of data in it. As indicated by the Date in column A. Each month, I need to go to this sheet, remove the data that is two months old, and paste in the data that is new (aka the value in cell A1 of Sheet_1).


further clarification: so the data on Sheet_3 contains the most recent month's data. so it would only contain data for 9/30/19 in this example. yes, import everything from Sheet_3, except the headers, so takes the data from row 2 until the last row of data. yes, Sheet_2 table is sorted by data. so you'll have like 400 rows of data for 8/30/19, and then 400 rows of data for 7/30/19

any help is greatly appreciated

 

[shknbk2]

How about somethink like this? It uses autofilter to hide the data less than two months old and deletes the rest.

Sub rishijain11()
    Dim d As Date
    Dim sh1 As Worksheet
    Dim sh2 As Worksheet
    Dim sh3 As Worksheet
    
    Set sh1 = Worksheets("Sheet_1")
    Set sh2 = Worksheets("Sheet_2")
    Set sh3 = Worksheets("Sheet_3")
    
    d = sh1.Range("A1")
    d = d - 1
    d = DateSerial(Year(d), Month(d) - 2, Day(d))
    With sh2
        .Activate
        .Range("A1:A3").EntireRow.Insert
        .Range("A1").Value = "Report Date"
        .Range("A2").Value = "<=" & d
        .Range("A4").CurrentRegion.AdvancedFilter Action:=xlFilterInPlace, CriteriaRange:= _
            Range("A1:A2"), Unique:=False
        .Range("A4").CurrentRegion.Offset(1, 0).SpecialCells(xlCellTypeVisible).EntireRow.Delete
        .ShowAllData
        .Rows("1:3").Delete
        sh3.Range("A2").CurrentRegion.Offset(1, 0).Copy .Cells(Rows.Count, 1).End(xlUp).Offset(1, 0)
    End With
End Sub