Populate second sheet based on multiple criteria

[cyberwild360]

I am in the Marine Corps and I'm trying to make my life easier.

I have an Alpha Roster which has about 120 rows of Marines info which spans from Column A:AP. The rows change often depending on Marines coming and going from the unit.

I am not good with VBA but excel formaulas I consider myself decent....

How do I have a second sheet in the workbook display the first 4 columns (A:D) along with the 6th column (F) on the second sheet based on a value in the 6th column and in the 10th column (J). The formula if it could be put into a formula would look like this:

if F4's date is within a year from today and J4 is blank or "N" populate Columns A:D and Column F to the second sheet.

I can't attach the source doc becuase of privacy but if truly needed I can create a dummy one. Please - any help would be greatly appreciated.

 

[Legacy 234512]

date within year which way past or future

 

[Legacy 234512]

this will do

Option Explicit


Sub mcMacMarine()
    
    Dim ws1 As Worksheet, ws2 As Worksheet
    Dim i&, j&, lastRow&
    Set ws1 = ThisWorkbook.Sheets(1)
    Set ws2 = ThisWorkbook.Sheets(2)
    lastRow = ws1.Range("F" & Rows.Count).End(xlUp).Row
        
    'headers
    ws2.Range("A1:D1", "F1").Value = ws1.Range("A1:D1", "F1").Value
    
    For i = 2 To lastRow
        If (ws1.Range("F" & i).Value > DateAdd("yyyy", -1, Now())) And ((ws1.Range("J" & i) = "N") Or IsEmpty(ws1.Range("J" & i))) Then
            ws1.Activate
            ws1.Rows(i & ":" & i).Select
            Selection.Copy
            ws2.Activate
            ws2.Rows(ws2.Range("F" & Rows.Count).End(xlUp).Row + 1 & ":" & ws2.Range("F" & Rows.Count).End(xlUp).Row + 1).Select
            Selection.PasteSpecial Paste:=xlPasteAllUsingSourceTheme, Operation:=xlNone, SkipBlanks:=False, Transpose:=False
        End If
    Next i
    
    For i = 5 To 1000
        For j = 1 To ws2.Range("F" & Rows.Count).End(xlUp).Row
            If i <> 6 Then ws2.Cells(j, i).ClearContents
        Next j
    Next i
    ws2.Activate
    ws2.Range("F1").Select
End Sub