If...Then...Else to Copy Value from one cell, PasteSpecial Value to growing list on different worksheets

[arg123]

Hello,

I am slowly building my knowledge up by trial and error and tons of money on books. However, I cannot seem to figure this out.

I have a workbook with 4 sheets, Home, Team 1, Team 2, and Team 3.

On the Sheet "Home" I have a simple 4 cell system. "C9" is a drop down box with 18 participants' names. "C11" auto-populates with the team the selected person is on by VLOOKUP from a list offscreen. "C13" is a dropdown box with with reasons why they get points. "C15" auto-populates with the points associated with the selected reason.

The submit button runs a macro that will be a long series of If...Then...Else functions (unless someone knows how to better accomplish the task).

What I would like the script to do is: if the participant selected is "Jason" (Team 1) then the cell with the reason will copy the value (worksheets("Home")) and pastespecial to the "Team 1" worksheet in the first available open cell in column E (the first cell pasted to would be the 8th in the column). If "Sam" then the reason would copy and paste to "Team 1" worksheet in the first open cell in column H, starting at H8. If "Chris", then the reason would copy and paste to the "Team 1" worksheet in the first open cell in column K...and so on.

The reason the copy value is because I don't want it to copy the whole dropdown box. The reason for the paste special is beacuse I would like it to paste with the same formatting as the destination cell.



So far the script looks like this:

Sub submit_macro()
Dim LR As Long, i As Long
With Sheets("Home")
LR = .Range("C" & Rows.Count).End(xlUp).Row + 1
For i = 1 To LR
With .Range("C" & i)
If .Value = "Jason" Then
Sheets("Home").Range("C13").Copy Destination:=Sheets("Team 1").Range("E8")
ElseIf .Value = "Sam" Then
Sheets("Home").Range("C13").Copy Destination:=Sheets("Team 1").Range("H8")
ElseIf .Value = "Chris" Then
Sheets("Home").Range("C13").Copy Destination:=Sheets("Team 1").Range("K8")
End If
End With
Next i
End With
End Sub

Some was borrowed. This will be long once it is all entered. It does not currently copy the value or paste special. Instead, it pastes the whole dropdown box and with the source formatting. It also doesn't add the most recent paste to the nect open cell in the column. These are things i would like to correct.

If there are any questions, I would be happy to answer them.

I have pictures but not upload-able it seems.

Thanks in advance for getting all the way through this and for any help you may have to offer.

 

[My Aswer Is This]

Your correct Tony.
My validation list was from a list of values typed in comma separated.
Thanks for that piece of information.

 

[arg123]

Thanks again guys for all your help here. I have made a few adjustments as suggested. I haven't added the data validation yet. that is something I will tease out as I gain great understanding of what I'm doing.

I attempted to add the last row function in the box but it returned an error 1004, Application-defined or object-defined error. This has been one of the more difficult things to figure out. Of all I've read I need to add something like this to define LastRow:

<code style="margin: 0px; padding: 0px; border: 0px; font-family: Consolas, Menlo, Monaco, "Lucida Console", "Liberation Mono", "DejaVu Sans Mono", "Bitstream Vera Sans Mono", "Courier New", monospace, sans-serif; white-space: inherit;">Dim lastrow as long
lastrow = activesheet.Cells.(Rows.Count, "E").End(xlUp).row + 1
</code>Cells(lastrow, "E").Value = "Your Value here"

I'd guess that Lastrow As long would be entered at the top with the other Dim statements:

Dim LR As long, i As long, lastrow As Long

Then I figure there is issue with where to enter "lastrow = activesheet.Cells.(Rows.Count, "E").End(xlUp).row + 1" statement withtin the following heirarchy:

With Sheets("Home")
LR = .Range("C" & Rows.Count).End(xlUp).Row + 1
For i = 1 To LR
With .Range("C" & i)
If .Value = "Jason" Then
Sheets("Home").Range("C13").Copy
Sheets("Team 1").Range("E" & LastRow).PasteSpecial xlPasteValues 'red didn't work, prompted error 1004

And the following would replace

Sheets("Team 1").Cells(lastrow, "E").Value = "your value here"
where mty value there would be = Sheets("Home").Range("C13").Value

But that doesn't seem to work either. So as mentioned, I think I am putting things in the wrong place. Or maybe using the wrong code. Where some of the arguments start and where the code is to be placed still kind of throws me. I've read through 3 Mr. Excel books and I kind of understand the basics but structure still kind of gets me.

Anyway, any additional input would be very helpful.

Again, all info is much appreciated and thank you for your time and patience.

Regards

 

[arg123]

Again, tried to put it into the right format and well spaced and what-not but it didn't stick. How do I do that to make it easier on anyone else looking at it, helping me out?

Thanks

 

[My Aswer Is This]

I really think what your attempting to do is difficult for someone not use to using loops. I would learn using loops on a easier job till you know how loops work.

In your below script you use LR and then use lastrow.
You have to use one or the other

With Sheets("Home")
 LR = .Range("C" & Rows.Count).End(xlUp).Row + 1
 For i = 1 To LR
 With .Range("C" & i)
 If .Value = "Jason" Then
 Sheets("Home").Range("C13").Copy
 Sheets("Team 1").Range("E" & LastRow).PasteSpecial xlPasteValues

Fixing just that is not going to fix your problems but it shows you something.

Trying to be kind but I think you should learn to ride a bike before trying to ride a Motorcycle.

If your dealing with 16 different names going into 16 different columns you will need 16 different Lastrows unless some other plan is developed.

 

[arg123]

I appreciate your remarks. I feel a little better knowing this is a little more complicated. Thanks for your input.

 

[My Aswer Is This]

arg123.
If you are still monitoring this thread please check back in here. I may have a answer now.

 

[arg123]

I am indeed, and very interested to see what you have.

I keep playing with it, reading more about loops, still struggling with fully comprehending how the last row function works.

Thanks for continuing to attend to the code and my issues with it. I'm quite thankful.

 

[My Aswer Is This]

There are a few things I have questions about:
1. In the drop down box does it say "Jason" or "Jason (Team1)" ??
2. I do not understand what the vlookup does.
3. And how do we know what team "Jason" is on?
4. And I'm not sure what you mean when you talk about "The reason"

You said:
C13" is a dropdown box with with reasons why they get points.

 

[My Aswer Is This]

And I read over this again. I do not understand this:

the cell with the reason will copy the value (worksheets("Home")) and pastespecial to the "Team 1" worksheet in the first available open cell in column E

Tell me what are we going to copy into the team sheet.
Do we copy the name and something else or what.