complicated auto populate possible or not and if so how. Solved/Closed

Question

Hello, I'm using excel to schedule for my business, I have all the customer information on the first sheet and would like to auto populate the other sheets by selecting a certain day in a drop down menu if that is at all possible. 
so when i select Monday from the service day column for a client, it will auto populate the Monday sheet with that clients information. 
Thanks in advance.

vcoolio · Answer

Hello Jmp2009,
A Worksheet_Change event code should do the task for you. The code is as follows:-

Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False

On Error Resume Next

If Intersect(Target, Columns(9)) Is Nothing Then Exit Sub

Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(3)(2)

Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub
When a day is selected from the dropdowns in any cell in Column I and then you click away (or press Enter or down arrow), the code will transfer the relevant row of data to its individual sheet.
To implement the code, right click on the main (input) sheet. From the menu that appears, select "view code" and in the big white field that then appears, paste the above code.
I've prepared a little example for you to play with at the following link:-
https://www.dropbox.com/s/s6kx6d4qssjlir9/Jmp2009%28Master%20sht%20to%20multi%20shts%2C%20worksheet_change%20event%29.xlsm?dl=0
Let us know how it works for you.
I hope that this helps.
Cheerio,
vcoolio.

vcoolio · Answer

Hello Jmp2009,
Yes. Can do. I assume that you mean you would like the data that has been transferred to its individual sheet deleted from the main sheet. Hence, the following minor adjustment to the code should do:-
Private Sub Worksheet_Change(ByVal Target As Range)

Application.ScreenUpdating = False
Application.EnableEvents = False

If Intersect(Target, Columns(9)) Is Nothing Then Exit Sub

Target.EntireRow.Copy Sheets(Target.Value).Range("A" & Rows.Count).End(3)(2)
Target.EntireRow.Delete

Application.EnableEvents = True
Application.CutCopyMode = False
Application.ScreenUpdating = True

End Sub
You'll see that I've added the delete line of code and an events handler just to prevent the code from going into an endless loop.
I also forgot to mention in my earlier post that you need to make sure that Column I is always selected last, after all data has been entered for any particular row.
Cheerio,
vcoolio.
P.S.: Here is the link to the updated sample:-
https://www.dropbox.com/s/s6kx6d4qssjlir9/Jmp2009%28Master%20sht%20to%20multi%20shts%2C%20worksheet_change%20event%29.xlsm?dl=0

jmp2009 · Answer

Thanks so much. works perfect. Now i can spend less time typing new clients info in!

vcoolio · Answer

Hello Jmp2009,
All sorted then including the little issue that arose?
Glad that I was able to help.
I am curious though. As the code is designed to transfer data from the main sheet to individual sheets depending on a criteria, why did you place the code in all the other sheet modules as well?
Cheerio,
vcoolio.

vcoolio · Answer

Hello Jmp2009,
I am somewhat confused as to what you are attempting. Yes, two separate Worksheet_Change events won't work in one sheet module. You also shouldn't be having to copy/paste manually anymore. You should only need to operate from your Master sheet as that is the purpose of this sheet.
Forget the first code all together and delete it from the sheet module. Just use the second code. You did place the code in a sheet module not a standard module?
In your dropdowns, add "Expired" to the list and make sure that your "Expired" sheet is spelt exactly the same. Once "Expired" is selected, the code will do the rest. If you are wanting to send completed contracts to the "Expired" sheet from the individual sheets, we may have to add a button to each sheet just for that purpose (or add an appropriate Worksheet_Change event code to each sheet module for that purpose).
Let us know your thoughts.
Cheerio,
vcoolio.

Complicated auto populate possible or not and if so how.

5 responses

Subscribe To Our Newsletter!