Transfer Data from One Sheet to another based on an if/then condition.

[Solved]
Report
-
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
-
Hello there,

I'm hoping to work with three lists (three sheets). First sheet, a master list, second sheet, a manager's list, third sheet, an employee's list.

In the Master list, I hope to include a column that has a drop down option between "manager" and "Employee". When a selection is made, I'd love for the whole line to be moved or copied to the respective list on either Sheet 2 "Manager" or 3 "Employee".

Can excel do this kind of function?
Please let me know if you require more clarification for my question.
- alyssa



System Configuration: Windows / Chrome 91.0.4472.164

2 replies

Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
Hello Alyssa,

A simple event code will do the task for you:-
Private Sub Worksheet_Change(ByVal Target As Range)

        If Intersect(Target, Columns(1)) Is Nothing Then Exit Sub
        If Target.Count > 1 Then Exit Sub
        If Target.Value = vbNullString Then Exit Sub
        
        Application.ScreenUpdating = False
        Application.EnableEvents = False
        
        Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(3)(2)
        Sheets(Target.Value).Columns.AutoFit
        Target.EntireRow.Delete
        
        Application.EnableEvents = True
        Application.ScreenUpdating = True

End Sub


Assuming that you have drop downs in all cells in column A of your data set with the values of Manager and Employee, once you make the required selection from the drop downs the relevant row of data will immediately be transferred to the relevant worksheet. The row of data in the Master sheet will then be deleted.
Not knowing how your workbook is set up, I've attached a mock-up sample at the following link to show you how this works:

https://wetransfer.com/downloads/9103f75393fe9388bc01ef418e93d1d420210726235522/1fedd2

Just select a value from the drop downs in Column A to see it work.

If you decide that this is how you'd like your actual workbook to work then let us know and we'll help you with implementing the code.
Please give us a proper description of how your Master sheet is set up.

I hope that this helps.

Cheerio,
vcoolio.
Hi Vcoolio,

This is brilliant! It does exactly what I imagined. I'd love to try to implement this in an Excel, but I do have to wonder first, would this function work still if I shared the document with a coworker and they opened it on their own computer?

Let me know, and if the answer is yes, I'd love to work with you to develop this!
Thanks!
- Alyssa
Posts
1320
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
October 8, 2021
239
Hello Alyssa,

If you share the document on an internal server anyone can work on it but only one at a time as far as I know. If you have it open, you'll be able to work on it, make changes etc. but anyone else who opens it whilst you are working on it will only be able to see it in 'Read Only' mode. If some one else needs to work on it then they'll have to ask you to save any changes and close the workbook so that they can then work on it. This is fairly common with internal servers but if you have a really clever IT team then they may be able to alter internal settings to allow anyone to work on it at the same time.

If your workbook is stored in cloud storage then macros (as in the code I've supplied) won't be supported and the workbook will need to be down loaded to a User's desktop for it to be worked on by said User (and macros will then work again).

Cheerio,
vcoolio.