Excel VBA Course
Excel VBA Course - From Beginner to Expert

200+ Video Lessons
50+ Hours of Video
200+ Excel Guides

Become a master of VBA and Macros in Excel and learn how to automate all of your tasks in Excel with this online course. (No VBA experience required.)

View Course

Filter Status Active with the date and Replace header column

0

Hi Everyone,

I'm stuck with my code. I hope you can help me. 

1. I want to filter "Last Day of Work" and "Active Status".

Example: IF "last day of work" >=TODAY then the "Status active" is YES.

2. I want to change the column header title and the position of the body.

3. I want to add "MY" in front of the Employee ID. Example: Employee ID is 0001. The result is MY0001.

------------------------------------------------------------------------------------------------------------------

Hi John,

Thank you for your guidance. Question no 3 is successful now.
For Q1, I want to do filtering with "Leave Date" and "Active Status". For Example, If Leave Date >= Today Then Active Status "Yes", "No".

For Q2, I want to change the column name and rearrange the position. For Example,
in current data, we have Employee ID | Title | First Name | Last Name. I want to change and rearrange Login | First Name | Last Name | Gender

Herewith I attached the full code.

Answer
Discuss

Discussion

Please put CODE tags around your code. To do that, edit your post, select your code and click the CODE button.
don (rep: 1979) Aug 10, '22 at 9:48 am
Nabila. 
I Iooked at your new file (but only on my mobile phone so no macros) and still not clear, given you show only 2 employee examples...

You say you want "filtering with "Leave Date" and "Active Status". For Example, If Leave Date >= Today Then Active Status "Yes", "No". Do you mean your result should be only employees whose Leave Date is non-blank and after today AND whose Active Status (column I) = "Active" (converted to "Yes" in column AB)? 
John_Ru (rep: 4022) Aug 11, '22 at 5:35 am
I give you some example. Staff X , leave date is 11/08/2022(today). The details of Staff X will be deleted. Also, other staff with the leave date AFTER the date today(11/08/2022) also be deleted. The result only appear the active staffs which is 12/08/2022 onwards. Yes, I want to convert Active to Yes.
nabila (rep: 8) Aug 11, '22 at 6:02 am
Please see revised Answer/ new file.
John_Ru (rep: 4022) Aug 11, '22 at 11:08 am
Thank you a lot, John. Your help means a lot to me.
nabila (rep: 8) Aug 12, '22 at 12:13 pm
Add to Discussion

Answers

0
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.

Discuss


Answer the Question

You must create an account to use the forum. Create an Account or Login