Copy and Paste using worksheet change

[arader1216]

ARader1216
Problem: Excel Macros in Visual Basic, Copy and Paste using a worksheet change

I am brand new to VBA and macros.The whole goal of the workbook is simple. I am attempting to create a database for my father's jobs that is very simple for him to enter the information and it will auto-fill the rest of the workbook. I have created a simple user form where he enters the data for the job. The data is copied to a sheet entitled "entry" which is sheet #25. (This will allow me to easily see every entry and fix any problems that he may create.) There are several different columns on the entry sheet. Columns E, G, I, K, and M are for sub names (stands for sub-contractor) . I have a sheet for each "sub" and I would like to have the whole row pasted to the corresponding sub sheet if one of those columns contain the code for that sub. This will be done each time an entry is made and automatically without pushing a command button, etc. (So, essentially if one of those columns contain "Mills" I would like the whole row to be copied and pasted to the next available row in the sheet for the sub mills (entitled 6Mills). I would like to do this for each of the subs. I would like the "trigger" cell contents to be the last name of the sub (as listed on the sheet title, without the number) The subs are all listed right before the entry sheet in the normal gray color. There are 13 total subs and they are the only ones with a number in their sheet title. This will get me started. I already have a very basic code started (listed below) but I can't even get that to work for some reason, so I didn't want to even try to do everything at once. If I can I would like to ask about other codes too once we get this problem fixed. THANKS SOOO MUCH!!

Please Login or Register  to view this content.

[smuzoen]

This would be a good candidate for Select Case. Based on the entry in column 5 would determine where the data is copied. Something like the following will copy the new entry to the correct sheet. In this example there is only one Case however based on the value in column 5 you would expand the code for all the possible entries. You need to be careful as well - on Sheet1 you have a worksheet_change event - if you copy data into a sheet that has a worksheet change event you will trigger that event in the target sheet so you should delete the code from Sheet1 (Tielsen) and try the code below.

Please Login or Register  to view this content.

Here is a link to the Select Case command

[arader1216]

Okay, I filled out the code. But it is still not working. I am under the impression that you should be able to hit the "play" button the code will execute. (That is what happened with my user form.) But it is not working that way with this code. When I hit the play button in Visual Basic it is just asking me to basically create a new macro. I even went so far as to create a new macro and then I hit the play button it asked me to create a new macro again and it still did not do anything when I started to enter the info. When I did not hit the play button and just strictly hit save in VBA and went to enter the info into the sheet, I noticed that the developer tab went pale (like it is processing) and then back to normal when I quit keying. When I get to the column 5 as listed in the macro, then I enter one of the values and none of them trigger anything. The other pages just stay blank. Are there things I need to do in order to be sure that the code executes? How can I make it always run so that when my dad opens the document and enters a job it automatically executes and fills out the sheet? Thanks so much! I really appreciate it!

BTW - Here is the code I ended up with:

Please Login or Register  to view this content.

and I did delete that code on sheet one, but I will eventually want to have a code there. Eventually I would like the info that is put on the page for the sub to trigger a formula that auto calculates the payment for the worker's comp and liability, and then that amount is copied to a totals page. Does that make sense?

[smuzoen]

You have done well with the Select Case however be careful with Sheet Names - for example The Tiesen copy would not work due to the fact the sheet you want to copy to is called 1Tiesen and you had 1 space Tiesen. I have added combobox to your form and a button to start your form off on your entry page. Comboboxes will make it easier so the names of the Selection will be correct - I set the combobox values in a module. Have a look at the attached workbook. You obviously still have a bit of work to do such as data validation on the form and some error handling however 1 step at a time. See attached workbook (I have not checked every Case command however 1 have checked first few and they copied fine) - again be careful on worksheet_change events when you copy to sheets as I previously said. As well the order that you enter the data into the Entry sheet is important. The combobox value is added last otherwise the whole row will not copy.

[arader1216]

Okay, I am feeling pretty ignorant. (I guess that's because I am ) I copied the module for the comboboxes into the entryform. I got all of the comboboxes working properly. However, I still cannot get the copy function to execute. It did not work even when I just put in info straight from what you sent. I am really sorry that you are having to go over this again. But, I checked all of the sheet names, the values in the dropdown box and everything...
I noticed that it selected the rows on the specific pages.. so it has to be in the copy and paste part..
Is there any certain mode I have to be in or anything?
Here is the code one more time.

Please Login or Register  to view this content.

Thanks so much for your time!

[smuzoen]

You have placed in multiple comboboxes - Does each Subcontactor details get copied to each subcontractor sheet or is it only the Sub1 value that determines where the row gets copied to? You had TargetColumn = 5 so I assumed you wanted the entire row copied to the sheet determined by the value in column 5 on the entry sheet. Run the attachment I have placed and do not change any code. Make sure that macros are enabled. The important question is that do you want the entire row copied to the sheet dependant on the value in column 5 OR do you want each subcontractor information (determined by the comboboxes) to the respective subcontractor sheet.
I have deleted all the textboxes that were not required on the form (you had just placed comboboxes over the top of them) and I redesigned the form as well to make it a little easier to use. If you be VERY specific about what should happen when the data is entered into the form i.e. What data should go Where when the Add Job button is pressed. Again make sure macros are enabled and just open the workbook I have attached and change NO code and tell me what is going wrong and what has to change. Perhaps even add a worksheet showing me exactly what you expect to happen when The Upload Job button is pressed.

[arader1216]

Okay. I opened your file and made sure that the security said that all macros should be enabled. I went to the VBA page, found the form EnterJobs, pushed play, entered the info, and pressed Add Job. Then I went to each page to see if the info went where it was supposed to go, and it went only to the entry page.

I want the info to go from the form to the entry sheet. Then, I realized there is a problem. Maybe you can help me. If I copy the entire row to the respective pages, then I copy the information for other subs in addition to the information for that sub. How can I copy the date, job, adjuster, amount, whichever two sub columns apply to that sub, and then materials, o/p, and dump only to the respective sheet for that sub as determined by the combo boxes? i.e. if column 5 has tiesen in it I want the date, job, adjuster, amount, column 5 and 6 (because the column 5 box said tiesen and col 6 is the amount that goes with it), materials, o/p, and dump to the page for tiesen. If it was column 7, then it would be the same, but columns 7 and 8. Does that make sense?

I hope this is not too much trouble and I sincerely and greatly appreciate everything!

[smuzoen]

That is what I have been trying to find out - what you have explained above makes more sense. Leave it with me and I will fix it for you and upload a workbook for you to try. The way you initially had coded this lead me to believe you wanted the entire row to go to one Sub only - this makes much more sense. Will get back to you - at work at moment so when I get a little free time will fix it for you. Talk soon

[arader1216]

You are so very kind! Thank you! I will have more questions once I get more information from my father. I have to get some dates for the quarter cut-offs and some percentages for the worker's compensation and liability insurance before I can go there. I also might need to fix what we are working on if my dad decides to be indecisive, but perhaps I can do the latter on my own. Will this be ok? I am so thankful that there are nice people out there and I appreciate this more than you know!~

[smuzoen]

I have made a lot of changes to help you on your way however you still have work to do. In the form your currently MUST enter values for ALL comboboxes - I do not know if there will always be 5 Subs for a job but the way it is coded at the moment the validation is written that ALL comboboxes and ALL textboxes must have values - you can change that if you like. There is no validation to check that Sub1 is not the Same as Sub2 (or any of the Subcontactors double up - you will need to decide about that) - I have written enough that you should be able to get further on your way. I will not write the whole project for you however I will help you so if you have specific questions do not hesitate to ask. I have placed all the code in modules and in the form rather than worksheet_change events. Really what you are doing is not meant to be done in worksheet code. Try to confine your work to modules and forms however if you do direct entry into sheets or have specific needs then worksheet_change events can be very useful. Anyway have a look at the attached workbook and try to modify the code to your needs and if you have questions just ask. I also needed to change the names of some sheets - avoid using "&" in sheet names and in value/control names - excel will get confused.
PS: I have done a bit of testing so you will need to ensure all the various Subcontractor details work. You should also think about formatting the entries to make it look a little nicer ($, currency etc etc)

[arader1216]

This is WONDERFUL! Thank you soo much! I do have one question so far. I noticed that no matter whether I make a new button, or I use the one that you provided, the info copies on the page that the button is on. Ideally I would like a separate page, blank and at the beginning, to put the button because I want to hide the "entry" page from my dad. This is basically a way for me to see everything he has entered so that I can figure out any problems. How can I tell the button to paste the info on the entry page and move the button to a new sheet I created and moved to the front (sheet 1)? Thanks again!

[arader1216]

Okay, so my father has changed everything on me, imagine that. So I wanted to ask you a few questions about how all of this code works so that I can change everything to fit what I need. I am not understanding how it works. Can you explain the basics of how this is written? For instance I see that you are naming ranges and then referring to them later, but they are not what I am familiar with. Is there a place that I can go to look over this kind of code and learn how to alter it?
I wanted to include the code that you gave me, with a few changes, not as a re-write.. but as a reminder of what you sent, as it was in a document. But I had to leave it off because it was too many characters.

I also added a picture of what the new user form looks like. I still need it to copy the same way, but now it is o/p, labor, dump, and materials per sub as opposed to once per job. (I can't figure out how to add that in and make sure that everything is correct as far as the names of all of the textboxes and everything.

I think the picture will make it clear as to what I am needing, if you will help me a little.

I GREATLY appreciate all of your help, and if you do not want to help me I will totally understand.

[arader1216]

I have figured everything else out on my own, but I need desperate help with the command button to show the form I have created. For some reason no matter where I put the button, it pastes the information on the same page as the button (I want to hide all of the "entries" from my father because this is my safeguard to know if he makes a mistake.) So I want the "show user form" attached to a command button with the the caption "enter a job" on Sheet 1 (not renamed) located at the very beginning of the workbook. Then I would like the entries to paste on sheet 25(entry). I also have attached the document. I know there are probably some straggling subs and stuff because I have been playing with this button thing, but I have everything working smoothly with that exception, so I don't care. LOL! (My dad has changed everything, and I mean everything twice now but I got this! ) Can you please help me with this little issue? Thanks so much!

{For some stinking reason I have not been able to do any attachments since they "fixed" everything. Unfortunately, I was unable to attach the document! Sorry!!}

[smuzoen]

The reason that everything is pasting to the sheet with the button on it is because it is the active sheet. What you need to do is when you copy data is to nominate not just the range but the worksheet name as well. If I use the following

Please Login or Register  to view this content.

If will paste the data onto the sheet that is active (the sheet containing the button). However if I do this

Please Login or Register  to view this content.

It will paste to the sheet called data. It is all about referencing. Excel will always paste to the active sheet unless you tell it otherwise - you need to reference the sheet where the data is going to and in this case I have used "ws" to refer to the sheet called Data
If you use the method of worksheetname.Range.Paste as your code the data will end up on the page you want.
The other final way is to use the sheet number - In your case Sheet 25

Please Login or Register  to view this content.

Use the index number of the sheet to reference where the data is entered
Hope this helps.
Anthony
“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”

[arader1216]

Got it! Wonderful!

[arader1216]

Please Login or Register  to view this content.

[arader1216]

Please Login or Register  to view this content.

[smuzoen]

What is the problem you are having? Is there a specific bit of code causing you problems?

Anthony

[arader1216]

Yes, I am having trouble with the destination part of the copy and paste. I set the destination of the initial paste (from the form) to go to the entry sheet, but now, it still wants to copy from the first sheet when it goes to copy and paste the second time. (I do believe from the active sheet, but I am not sure.) I cannot figure out how to get the code to work properly so I posted what I thought to be the relevant code. Can you help me? I wanted to try and do it on my own, but I just seem to be making a mess.

I also have another question. I can manage to paste the information from the pages to the quarterly page, but I was wondering what the best way was to accomplish this last major goal.- Okay, once the information hits the sub page, the information is going to be used in a simple formula to calculate the amount paid in worker's compensation and liability insurance. These amounts will be in the next two consecutive columns.This total (per job) of all the subs and their due payments will be transferred to one spot on the totals page. i.e. tiesen and mills both worked on this job. I would like the total of the payments due (liability and worker's comp are two seperate payments and should be posted seperately) in two separate columns pasted to the totals page so my dad knows per job what he is paying. What is the easiest, best way to accomplish this? If you can just get me started, I would be sooo greatfuL!

[smuzoen]

Can you upload your workbook for me so I can have a look. It would make things easier. It's great that you have had a go. You learn a lot by making mistakes
errando discimus

Anthony

[arader1216]

Here it is. I had everything working great with this small exception which now makes the code not run. I am impressed with my progress so far.. I just wish I could have done this..

[smuzoen]

I have fixed the exception - the problem is with how you were setting the range using cells so that is fixed. Strictly what you were doing was correct but it can cause problems. I have fixed the form so it gets completely cleared when you add the job and hopefully the second paste issue is fixed.

amount paid in worker's compensation and liability insurance.

Let's leave this until the main problem is fixed which I hope it is now. See attached workbook

Hope this helps.
Anthony
“Confidence never comes from having all the answers; it comes from being open to all the questions.”
PS: Remember to mark your questions as Solved once you are satisfied and rate the answer(s) questions.”

[arader1216]

This is what I get..error.JPG
That is what I was getting before.. I did not change anything. I just opened the attachment, hit the enter job button to enter a job and make sure everything was good. Sorry..

[smuzoen]

I just downloaded what I uploaded and ran it in Excel 2010 by going to first page and hitting Enter Job button and I got no error. Just download it again and make sure you open the file and not a previous one. Then if you get the error use the debug process. Right click on the button on the first page and select Edit Macro. It will open up the code window. Left click to the left of Load Enterjob in the margin and this will add a breakpoint. A brown dot will appear in the margin to show a breakpoint is added. Then hit F8 and keep hitting F8 - this will allow you to step through the code one line at a time. Find which line of code is causing the error and report back. Debugging is a good way to narrow down the problems in code.
Let me know if you have trouble debugging the code. The important thing is to find the line causing the error. However download a fresh copy - copy attached to this post.

[smuzoen]

-duplicate post delete

[arader1216]

Public Sub Button1_Click()
Load (EnterJob)
EnterJob.Show
End Sub
The bolded line is the line that is giving me problems. Could there be a problem with my excel or settings because I just keep getting random errors. It's kind of ridiculous.

[smuzoen]

It should not be written like that - I have no idea what has happened - it is though the file you are downloading is corrupted. It should be

Please Login or Register  to view this content.

The should be NO BRACKETS around EnterJob - it should appear exactly as above.

[smuzoen]

Here is some links - Ensure your Excel 2010 (Office 2010) is up to date - make sure all service packs/updates are installed - go to Microsoft Update Site and install all Updates. You can also do a repair of Excel 2010 - try these links

http://support.microsoft.com/kb/2460049
http://office.microsoft.com/en-us/ex...010357402.aspx
http://obieosobalu.wordpress.com/201...gnostics-tool/

Make sure all your Windows and Excel updates are installed then do a repair of Office 2010.

[arader1216]

had no updates but performing a repair... be back shortly.

[arader1216]

Okay, getting the same error as before (error jpg) - downloaded the attachment again. It was corrupted still, so i swapped the code for what you gave me and now I am getting this error.. I dunno what is going on...

[arader1216]

The form initiates, but when I click "add job" within the form, it gives me this error. I tried to debug but i kept getting "stuck" on this same section of code - it just kept going through this section over and over.. I am assuming this is the error, but that's being assumptive. What do you think is up?

Please Login or Register  to view this content.

Also, I know that this is the code for the combo boxes and they are fine. So that's why I said it was assumptive. But, I could not get past this section of code to attempt and debug like you requested. I am really sorry all this junk is going on..

[arader1216]

I cannot go to page three of the posts..

[smuzoen]

That loops over all the controls and finds the comboboxes and adds the values - this won't be the error. Place a Breakpoint next to EnterJob.Show (not the load command) and then hit F5 - the code will run and load the comboboxes then stop just before showing the form. Then start hitting F8 again and see where the error occurs. We need to narrow down where the error is actually occurring. I have added more error handling to the new version. You are using Excel 2010? 32bit or 64bit version? Use the attached workbook. Reboot your machine as well.

[arader1216]

32 Bit.. Excel 2010..

[arader1216]

I can't get the debugging thing.. I think I am just frustrated by the whole project right now and I have not made any progress in a while. I am still far away from finished and I dunno if I am going to be able to actually do this.