Assumptions for this code:
1. Employee Inventory sheet has heading in row one so it will start checking the data in row 2.
2. Column Q does not have any empty cells.
3. EEs sheet will have headings in row one so the data will start copying in row 2.

Set i = Sheets("Employee Inventory")
Set e = Sheets("EEs")
Dim d
Dim j
d = 1
j = 2

Do Until IsEmpty(i.Range("Q" & j))

If i.Range("Q" & j) = "TERM" Then
d = d + 1
e.Rows(d).Value = i.Rows(j).Value

End If
j = j + 1
Loop

This code is really helpful!

I'm having problems using it to copy and paste using more than one IF statement.

For example...

Set a = Sheets("Sheet1")
Set b = Sheets("Sheet2")
Set c = Sheets("Sheet3")
Dim x
Dim z

x = 1
z = 2

Do Until IsEmpty(a.Range("B" & z))

If a.Range("B" & z) = "TERM" Then
x = x + 1
b.Rows(x).Value = a.Rows(z).Value

Else

If a.Range("B" & z) = "ACTIVE" Then
x = x + 1
c.Rows(x).Value = a.Rows(z).Value

End If
End If
z = z + 1
Loop
End Sub

The code does work but doesn't remove the blank lines between the copied data - how do I remove these?

Many thanks