Transfer Data from One Sheet to another based on an if/then condition.
Solved/Closed
apet134
-
Jul 26, 2021 at 04:59 PM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Jul 28, 2021 at 02:51 AM
vcoolio Posts 1404 Registration date Thursday July 24, 2014 Status Moderator Last seen September 15, 2023 - Jul 28, 2021 at 02:51 AM
Related:
- Transfer Data from One Sheet to another based on an if/then condition.
- Transfer data from one excel worksheet to another automatically - Guide
- Free fire transfer - Guide
- Little alchemy cheat sheet - Guide
- Google sheet right to left - Guide
- Vba copy data from one sheet to another if a condition is met - Excel Forum
2 responses
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Updated on Jul 26, 2021 at 08:00 PM
Updated on Jul 26, 2021 at 08:00 PM
Hello Alyssa,
A simple event code will do the task for you:-
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.
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.
vcoolio
Posts
1404
Registration date
Thursday July 24, 2014
Status
Moderator
Last seen
September 15, 2023
259
Updated on Jul 28, 2021 at 02:55 AM
Updated on Jul 28, 2021 at 02:55 AM
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.
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.
Jul 27, 2021 at 10:56 PM
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