Thread: Copy and Paste using worksheet change

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!!

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = ("Column5") Then
If Target.Value = "Mills" Then
    Worksheets("Sheet25").TargetRow.Copy
    Worksheets("Sheet24").Rows(1).Select
    Worksheets("Sheet24").Paste
End Sub

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.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
Select Case Target.Value
Case Is = "T"
    Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("1Tiesen").Range("A" & Worksheets("1Tiesen").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case is = "B"
'do copy to relevant sheet
Case is = "Y"
'do copy to relevant sheet
   End Select
End If
End Sub

Here is a link to the Select Case command

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:

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
Select Case Target.Value
Case Is = "Tiesen"
    Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("1 Tiesen").Range("A" & Worksheets("1 Tiesen").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case Is = "Jones"
    Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("2Jones").Range("A" & Worksheets("2Jones").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case Is = "TN"
    Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("3TN").Range("A" & Worksheets("3TN").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case Is = "Mie"
    Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("#4Mie").Range("A" & Worksheets("#4Mie").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case Is = "Mills"
    Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("6 Mills").Range("A" & Worksheets("6 Mills").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case Is = "Robertson"
    Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("7 Robertson").Range("A" & Worksheets("7 Robertson").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case Is = "Benley"
    Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("8 Benley").Range("A" & Worksheets("8 Benley").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case Is = "SAT"
Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("9 SAT").Range("A" & Worksheets("9 SAT").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case Is = "Roscoe"
    Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("10 Roscoe").Range("A" & Worksheets("10 Roscoe").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case Is = "L&"
   Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("11 Lemons&").Range("A" & Worksheets("11 Lemons&").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case Is = "LC"
   Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("12 Lem Con").Range("A" & Worksheets("12 LemCon").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case Is = "Chit"
   Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("13 Chittum").Range("A" & Worksheets("13 Chittum").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
   End Select
End If
End Sub

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?

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.

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.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target.Column = 5 Then
Select Case Target.Value
Case Is = "Tiesen"
    Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("1Tiesen").Range("A" & Worksheets("1Tiesen").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case Is = "Jones"
    Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("2Jones").Range("A" & Worksheets("2Jones").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case Is = "TN"
    Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("3TN").Range("A" & Worksheets("3TN").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case Is = "Mie"
    Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("#4Mie").Range("A" & Worksheets("#4Mie").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case Is = "Jackson"
    Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("5Jackson").Range("A" & Worksheets("5Jackson").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case Is = "Mills"
    Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("6 Mills").Range("A" & Worksheets("6 Mills").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case Is = "Robertson"
    Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("7 Robertson").Range("A" & Worksheets("7 Robertson").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case Is = "Benley"
    Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("8 Benley").Range("A" & Worksheets("8 Benley").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case Is = "SAT"
Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("9 SAT").Range("A" & Worksheets("9 SAT").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case Is = "Roscoe"
    Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("10 Roscoe").Range("A" & Worksheets("10 Roscoe").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case Is = "L&"
   Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("11 Lemons&").Range("A" & Worksheets("11 Lemons&").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case Is = "LC"
   Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("12 LemCon").Range("A" & Worksheets("12 LemCon").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
Case Is = "Chit"
   Worksheets("entry").Rows(Target.Row & ":" & Target.Row).Copy
     Worksheets("13 Chittum").Range("A" & Worksheets("13 Chittum").Cells(Rows.Count, "A").End(xlUp).Row + 1).PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False
   End Select
End If
End Sub

Thanks so much for your time!

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.

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

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)

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!!}

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

Range("A6").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

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

Set ws = Worksheets("Data")
    ws.Range("A6").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

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

Sheets(25).Range("A1").PasteSpecial Paste:=xlPasteAll, Operation:=xlNone, SkipBlanks:= _
        False, Transpose:=False

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.”