Imagine I'm a recruiter (moving candidates into other sheets)

[Mievo]

Good day,

thank you in advance for reading through my problem.

Imagine I am a recruiter. And I put all my candidates into an Excel sheet called "Candidates". I also have a sheet called "Don't hire" and "Done". I put some details of my candidates into the "Candidates" sheets. I also have one cell with a dropdown in $F5, saying "Yes" or "No" ("do you want to hire?").

I would love Excel to move the candidates who have a "No" on the question "do you want to hire?" to the sheet "Don't hire" (and add them below the candidates who are already in the "Don't hire" list). The candidate should be moved and then deleted out of the "Candidates"-list, so I only have him in the "Don't hire" list. The same procedere then for candidates in the "Don't hire" list after I select "Rejected? > Yes".

How can I do this? Any idea? I'm new to VBA, I really like to learn it but I have no clue where to start.

Thank you in advance!

[Mumps1]

The same procedure then for candidates in the "Don't hire" list after I select "Rejected? > Yes".

In which column in the "Don't hire" list do you select "Rejected"? Once you select "Rejected" do you want to delete that person from the "Don't hire" list?

[Mievo]

I don't have it yet, it's just the idea I have in mind. Didn't want to create everything without being able to then implement it. But since I have knowledge in Python, I guess I will be able to change small things like this.

But I can create everything and give you a concrete answer within 10 minutes.

Yes, I want them to be deleted then. In the best case scenario, it will automatically clean up the list and not leaving blank spaces in there.

Thank you in advance for your help!

[Mievo]

So:
Candidates are starting from row 6 (first item: Name [row 6, col B - I)

Same structure for "Don't hire" then (original name: "Absagen" [it's German])

If I would have the strucutre once, I can implement it for the "Don't hire" > "Done" on my own, I guess.

[Mumps1]

For clarification:

Do you have headers in row 6 or do the names start in row 6?
Is the dropdown in F5 and does it include "Yes" and "No"?
In which column in the "Don't hire" list do you select "Rejected"?

The easiest way for me to see how your sheets are organized would be to attach a copy of your file. Explain in detail what you want to do referring to specific cells, rows, columns and sheets using a few examples from your data (de-sensitized if necessary). See the yellow banner at the top of this page for instructions to attach a file.

[Mievo]

Cheers. I have the following relevant sheets: Overview, Absagen, abgesagt.
Overview = Candidates, Absagen = Don't hire, Abgesagt = Done

I want the candidate (e.g. Ron) to be moved to "Absagen" once I select for H6 the drop-down selection "Ja".
When moving the candidate Ron to "Absagen", I want him automatically to be deleted out of the Overview (without leaving spaces). I only need the information from B6 - E6 to be moved, the rest can be deleted.

In "Absagen" I want the same thing to be happen when I select "Abgesagt?". Once selected "Ja" (= Yes), I want the candidate to be moved to "Abgesagt", but only the information from B5 to D5. In "Abgesagt", when the candidate is added, I want Excel to set the current date and save it for E5 ("Abgesagt am" which is translated "We rejected him at date XX.XX.XXXX).

I hope you get what I mean. If not, please let me know!


.. I just see that the file is too big and I have no clue how to reduce size. Can I share it via Discord for instance?

[AliGW]

Excel version: which 'newest one' do you have? There are several - please be specific in your profile. Thanks!

[Mievo]

Where can I find this information? I have Office 365. Thank you in advance for your help.

[Mievo]

I hope this works

[AliGW]

That's the information you need in your profile - Office 365.

We cannot work with pictures. There are instructions at the top of the page explaining how to attach your sample workbook.

[Mumps1]

We don't need all the data to be included in the file you post. 10 or 12 rows of data would be enough to show how the data is organized.

[Mievo]

Sorry for being so complicated! There we go. I appreciate your patience!

[AliGW]

Still waiting for the profile update.

[Mievo]

Done! Thank you

[Mumps1]

Try the attached file.

[Mievo]

Hello,

any chance that you send me the VBA code separate? I get an error message asking if I trust the source since there are errors that Excel want to fix. Since it is the working laptop, I cannot do this.

I would highly appreciate it! If not, I will download end test it with my private computer.

Thank you in advance!

[Mumps1]

Copy and paste this macro into the worksheet code module. Do the following: right click the tab name for your Overview sheet and click 'View Code'. Copy/paste this macro into the empty code window that opens up. Close the code window to return to your sheet. Select "Ja" in column G.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("G:G")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target = "Ja" Then
        With Sheets("Absagen")
            Range("B" & Target.Row).Resize(, 4).Copy .Cells(.Rows.Count, "B").End(xlUp).Offset(1)
            Target.EntireRow.Delete
        End With
    End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

Right click the Absagen sheet and repeat the above steps and copy/paste the macro below. Select "Ja" in column F.

Private Sub Worksheet_Change(ByVal Target As Range)
    If Intersect(Target, Range("F:F")) Is Nothing Then Exit Sub
    Application.ScreenUpdating = False
    Application.EnableEvents = False
    If Target = "Ja" Then
        With Sheets("Abgesagt")
            Range("B" & Target.Row).Resize(, 3).Copy .Cells(.Rows.Count, "B").End(xlUp).Offset(1)
            .Cells(Sheets("Abgesagt").Rows.Count, "E").End(xlUp).Offset(1) = Date
            Target.EntireRow.Delete
        End With
    End If
    Application.ScreenUpdating = True
    Application.EnableEvents = True
End Sub

[Mievo]

It works perfectly. Damn, thank you so much! And it seems like magic for me that this short code is doing this. Will definitely learn VBA :-)

[Mumps1]

You are very welcome.

[Mievo]

Any chance to save it in a variable for a time-period that I can add somewhere a button for instance that allows me to say "undo"?

[Mumps1]

Unfortunately, you cannot undo the actions of a macro. What exactly do you want to do

[Mievo]

No problem then. I just try to re-arrange the Macro and use it for some more actions (copying things from other sheets to other sheets), but I made a mistake and lost one data-example and thought "Hmm, you can't undo. Would be cool if it is possible". But all good.

Where do I set in the code from which cell on it should be pasted in?

[Mievo]

Already got it!

[Mumps1]

Sounds good.

[Mievo]

One question left, then I will be done with it I guess.

I try to implement another copy-function within the same worksheet (so not moving don't-hire-candidates to "Absagen", but moving good candidates to "interviews"). I tried to duplicate the Sub and adapt but. But when setting "Ja" (in col "H") nothing happens.

This is my current code:

And the last thing: How can I change the code that it is only moving things if I click on a button called "Move candidates" and not as soon as I put them on "Yes"?


Thank you in advance!

[Mievo]

Cheers mate,

I added a sheet called "Todo". I listed the stuff (with explenation) of what I would need.

Let me know what is do-able and not too much work!

Thank you in advance for all your help!

[Mumps1]

The Overview sheet has no data. Could you attach a copy of your file with some data?

[Mievo]

There you go

[Mumps1]

Do you actually enter a date in column I or do you select "Ja" to indicate that a job is on hold?
I don't see a "Suchen" button in the Overview sheet.

[Mievo]

I will enter a date in "I" to say when I have sent the on-hold message. Therefore it will allow to calculate if it is older than 10 days or not.
I didn't implement it yet since I didn't know how to create the Suchen-form.

[Mumps1]

Try the attached file. I have removed all the conditional formatting. The macros will now do all the work. I believe that I have taken care of all your requests except for the Suchen-form. Try to design the form to contain the information that you want and then describe in detail referring to specific cells, rows, columns and sheets how you want the form to work. Also, I believe that you don't want to transfer the data to the "Interview" sheet when you enter "Ja" in column H. I think you want a button to do this for all the rows containing "Ja" all all one time. Is this correct?

[Mievo]

Thank you so much, I'll check it out at home.

Yes exactly, that makes it easier to select multiple candidates and "recognizing" which one I selected. So I can then one-click move them all.

I will do. Thank you so much!

[Mumps1]

Try the revised attached file and post back when you have the Suchen-form problem resolved.

[Mievo]

Cheers again,

another question that popped up: How can I adapt that it is not automatically pushing someone from "Overview" to "Absagen" for instance as soon as I select "Yes", but it is pushing it when I select all the people I want to be moved to "Yes" AND click then a button for instance "Kandidaten verschieben" (= "Move candidates")? :D

I use an adapted version of the code Mumps1 gave me:

Sorry for all the questions! I'm googling a lot but not finding all the solutions I need.

Thanks in advance

[Mievo]

Cheers mate,

thanks for all your effort and I'm so sorry, but it is not completely what I meant. I think I didn't explain it properly, I'm very sorry.

Attached you can find the Excel file. I explained in every sheet what I need to happen with stating an example. I hope this is clear.

[AliGW]

I expect you are referring to virtual bucks, but if not, please remember that this is a free forum where nether offering money nor soliciting it is allowed. All the help you get here is completely free.

[Mievo]

Thanks for your reply. Yes, it was just to show my appreciation. But to not lead to any confusion, I deleted it. Thank you

[Mumps1]

Try the attached file. It is difficult for me to test because the file has no data.