Selected Answer
Hi Nabila and welcome to the Forum.
On your question 1, you can filer for both status and Leave Date using the changes of code (in bold below) to filter dates > today if status is also active:
'<< existing code>>
CriteriaWildCard = ">=" & Format(Date, ws.Range("J2").NumberFormat)
ws.Range("R:AH").Clear
' filter for date after today and status
With RangeToFilter
.AutoFilter Field:=10, Criteria1:=CriteriaWildCard
.AutoFilter Field:=9, Criteria1:="=Active"
End With
' copy only visible cells
ws.Range("A1").CurrentRegion.SpecialCells(xlCellTypeVisible).Copy Destination:=DestinationRange
'<<existing code>>
where the important bit at the end is the addition of .SpecialCells(xlCellTypeVisible) so you only copy what the filter would show the user.
Note that it should be sufficient to use:
CriteriaWildCard = ">=" & Date
above but I think your system data format differs from mine so I used the format from that column to be sure. In the revised file attached, I added a third employee and set Leaving Dates for testing purposes.
On question 2, once you've operated on your data, you can "change position" of the Gender column using this code, at the end of the sub:
'<<existing code>>
'### rearrange columns
ws.Columns(24).Insert 'add a column
ws.Range("X1:X" & LRow2).Value = ws.Range("U1:U" & LRow2).Value ' copy from U to new X
ws.Columns(21).Delete ' remove old U
' autofit all columns ### just do it once, at the end
ws.Columns("A:AH").AutoFit
End Sub
Note, you need autofit only once, at the end (you had it in a couple of places but just wait until you've finished everything then autofit).
On question 3, adding MY to the EmployeeID to create Login in column T (=20), you can end your sub like this:
'<<existing code>>
' ## new add "MY" to create Login
Dim LRow2 As Long
LRow2 = ws.Cells(Rows.Count, 20).End(xlUp).Row
For i = 2 To LRow2
ws.Cells(i, 20) = "MY" & ws.Cells(i, 20)
Next i
'<<existing code>>
The full code is in Module 1 of the attached revised file.
Hope this fixes your problem or helps you to do so. Please remember to mark this Answer as Selected if it does.
