VBA - Moving Cells if Condition Met

[Scorpion1975]

Dear All,
Attached herewith the recruitment tracker I am planning to prepare and implement.

There are three tables in a single sheet namely 1) Vacancy Summary 2) Recruitment Status and 3) Joining Status.

Request your help in the following:

REQUIREMENT 1 – ON BUTTON CLICK
1. In “Recruitment Status” table, if row “OFFER LETTER ISSUED ON” is filled, then
a) The position offered is to be removed from table “Vacancy Summary" and "Recruitment Status"
b) The first three columns of "Joining Status" to be updated with the positions offered.

E.g. There are four vacancies of “Sr.Lab Chemist” in the vacancy summary table. You can find that three candidates have been offered the position. By clicking the button, three lines of “Vacancy Summary” is to be removed, three lines of "Recruitment Status" to be removed and three position in the “Joining Status” table will be updated on click of the button.

RECRUITMENT STATUS TABLE

SR. NO.	POSITION NAME	PROFILE DOWNLOADED ON	SOURCE	NAME OF THE CANDIDATES	PRESENT TAKE HOME SALARY (K/MONTH)	PRELIM. INTERVIEW DONE ON	PERSONAL INTERVIEW DONE ON	SELECT / HOLD / REJECT	SALARY OFFERRED (K/MONTH)	OFFER LETTER ISSUED ON
1	Sr. Lab Chemist	01-09-2020	ABC	Mr. XYZ ABC	123	02-09-2020	03-09-2020​	Reject	1234​
2	Sr. Lab Chemist	01-09-2020	Direct	Mr. ABC XYZ	123	02-09-2020	03-09-2020​	Select	1234​	04-09-2020​

JOINING STATUS TABLE

JOINING STATUS
SR. NO.	POSITION NAME	NAME OF THE JOINEES	DEPOSIT	DOCUMENTS	ESTIMATED DATE OF JOINING	ACTUAL JOINING DATE
1	Sr. Lab Chemist

REQUIREMENT 2 – ON BUTTON CLICK
2. Once the “Actual Joining Date” column of “Joining Status” table is filled, then
a) Sheet 2 will be updated with relevant information on click of the button.
b) In the Sheet2, “Days Took” columns will be calculated automatically (I will put formula there). I need rest of the information to be picked from Sheet1 and pasted under relevant Columns of Sheet2.
c) The entry from “Joining Status” table to be deleted.

SHEET 2 TABLE

SR. NO.

POSITION NAME

DATE OF RESIGNATION

DATE OF RELIEVING

PROFILE DOWNLOADED ON

DAYS TOOK (FORMULA)

PRELIM. INTERVIEW DONE ON

DAYS TOOK (FORMULA)

PERSONAL INTERVIEW DONE ON

DAYS TOOK (FORMULA)

OFFER LETTER ISSUED ON

DAYS TOOK (FORMULA)

JOINED ON

DAYS TOOK (FORMULA)

TOTAL DAYS TOOK (FORMULA)

Thank You All.

With best regards,

 

[onlyadrafter]

Hello,

can you supply the VACANCY SUMMARY table, and is it below the JOINING STATUS table or at the top of sheet 1?

 

[Scorpion1975]

Top of the table...

 

[Scorpion1975]

Top of the table...

SR. NO.	POSITION NAME	NEW POSITION / VACANT DUE TO RESIGNATION	DATE OF RESIGNATION	DATE OF RELEIVING OF EX-EMPLOYEE (IN CASE OF VACANT DUE TO RESIGNATION) OR DATE OF MANPOWER REQUEST (IN CASE OF NEW POSITION)
1	Sr. Lab Chemist (1)	Resignation	01-Jan-20	12-Aug-20

 

[onlyadrafter]

Hello,

This is a bit cumbersome, but should accomplish requirement 1 a and b.

Sub OFFER_LETTER_MOVE()
    Columns("K:K").Select
    Selection.Find(What:="OFFER LETTER ISSUED ON", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    TABLE_2_ROW = ActiveCell.Row
    Columns("A:A").Select
    Selection.Find(What:="JOINING STATUS", After:=ActiveCell, LookIn:= _
        xlFormulas, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:= _
        xlNext, MatchCase:=False, SearchFormat:=False).Activate
    TABLE_3_ROW = ActiveCell.Row
    
    For MY_ROWS = TABLE_3_ROW - 1 To TABLE_2_ROW + 1 Step -1
        If Not IsEmpty(Range("K" & MY_ROWS).Value) Then
            MY_SR = Range("A" & MY_ROWS).Value
            MY_POS = Range("B" & MY_ROWS).Value
            MY_CAN = Range("E" & MY_ROWS).Value
            Range("A" & TABLE_3_ROW).End(xlDown).Offset(1, 0).Value = MY_SR
            Range("A" & TABLE_3_ROW).End(xlDown).Offset(0, 1).Value = MY_POS
            Range("A" & TABLE_3_ROW).End(xlDown).Offset(0, 2).Value = MY_CAN
            Rows(MY_ROWS).Delete
        End If
    Next MY_ROWS
    
    For MY_ROWS = TABLE_2_ROW - 1 To 2 Step -1
        If Left(Range("B" & MY_ROWS).Value, Len(MY_POS)) = MY_POS Then
            Rows(MY_ROWS).Delete
        End If
    Next MY_ROWS
End Sub

If this works as expected, can look at the second requirement. If not working post back.