# Populate second sheet based on multiple criteria

#### cyberwild360

##### New Member
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.

### Excel Facts

Excel motto
Not everything I do at work revolves around Excel. Only the fun parts.
date within year which way past or future

this will do

Code:
``````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

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``````

Replies
3
Views
660
Replies
5
Views
829
Replies
1
Views
335
Replies
3
Views
110
Replies
1
Views
354

1,196,297
Messages
6,014,527
Members
441,826
Latest member
roudarreza

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back