Report

Complicated auto populate possible or not and if so how. [Solved]

Ask a question jmp2009 4Posts Saturday February 18, 2017Registration date February 21, 2017 Last seen - Last answered on Feb 22, 2017 at 03:56 PM by vcoolio
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.
See more 
Helpful
+1
plus moins
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.
Was this answer helpful?  
jmp2009 4Posts Saturday February 18, 2017Registration date February 21, 2017 Last seen - Feb 19, 2017 at 05:27 PM
Brilliant, thanks, is it possible that we can adjust the code so when I change the selection in Column "I" it will delete the previously copied data?
Reply
Leave a comment
Helpful
+1
plus moins
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
Was this answer helpful?  
jmp2009 4Posts Saturday February 18, 2017Registration date February 21, 2017 Last seen - Feb 21, 2017 at 09:58 AM
This is great, I was using the first code on sheet 1 and the other code on all other sheets, was working great, then all of a sudden I am getting error 13 mismatch and I don't know why.
Reply
jmp2009 4Posts Saturday February 18, 2017Registration date February 21, 2017 Last seen - Feb 21, 2017 at 10:07 AM
I really appreciate all your help. Thanks again
Reply
Leave a comment
Helpful
+1
plus moins
Thanks so much. works perfect. Now i can spend less time typing new clients info in!
Was this answer helpful?  
Leave a comment
Helpful
+1
plus moins
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.
Was this answer helpful?  
Jmp2009- Feb 22, 2017 at 09:07 AM
Still have the little issue. It seems I can't use both codes on the same work sheet only one or the other.

To answer your question I wished to keep the first sheet as a master list. When I get a new customer I add it there. I wanted to be able to copy to the other sheets for service days and to be able to shuffle them around on those sheets as required. For example when their contract runs out I can move them to the expired sheet while the master is uneffected.

I just haven't been using the first code on the master anymore and it seems to work. I just have to copy and paste from Master to another sheet then I can transfer using the code you provided. No big deal.
Reply
Leave a comment
Helpful
+0
plus moins
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.
Leave a comment

Member requests are more likely to be responded to.

Members can monitor the statuses of their requests from their account pages.

A CCM membership gives you access to additional options.

Not a member yet?

Sign up now. It takes less than a minute and is completely free!