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]

For one thing you need to use a line of code like this.
You cannot use Copy Destination

Sheets("Home").Range("C13").Copy
Sheets("Team 1").Range("K8").PasteSpecial xlPasteValues

For the rest of your question I get lost and will have to think some more about this.

 

[arg123]

First of all, thank you.

I am so disappointed in myself. I tried the exact same text and it wouldn't work. The error code stated that the first line was without an object. Fast forward to your reply and I copy and paste and it is pefect. The difference, .value was added prioir to .Copy. I had been told that would work and of course couldn't recognize that as the problem.

Well, I want to say thank you for your helping me out withthat part of it. It looks great and all I need now is to add the the next open cell in the column and I think I'm set. Such a simple fix.

Thanks again for your time. I know it is a long request for help.

 

[My Aswer Is This]

To put this in lastrow.
You would do something like this:

'Sheets("Team1").Range("K" & Lastrow).PasteSpecial xlPasteValues

 

[Michael M]

Don't have excel at the moment, but I'd suggest a Select case might be cleaner than a If / then
You will also need to incorporate the tips provided by MAIT
AND
Can you please use code tags when posting...it makes it easier to debug and read

ALSO
you should indent your code (see my example), also makes it easier to debug

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
            Select Case Range("C" & i).Value
                Case Is = "Jason"
                .Range("C13").Copy Destination:=Sheets("Team 1").Range("E8")
                Case Is = "Sam"
                .Range("C13").Copy Destination:=Sheets("Team 1").Range("H8")
                Case Is = "Chris"
                .Range("C13").Copy Destination:=Sheets("Team 1").Range("K8")
            End Select
        Next i
End With
End Sub

 

[arg123]

Thanks again to My Answer. I'll plug that in tomorrow to see the results. I'm guessing it'll be as quick and easy a fix.

Also, Michael, thanks for taking the time to run through my question as well. I apologize for not including the tags. When I copied the code into the box to submit, it was indented, seen as you reposted but when I submitted it reverted to left aligned. Sorry for that too. It may be something I'm unfamiliar with on the board here. I will read up a bit about the select case so I understand what I'm doing a bit better and attempt that as well.

As much as I'm looking for a fix to my problem here, I'm also trying to learn to identify problems easier as well as just gain a better understanding of what it is I'm doing. So thank you for your help and suggestions. They are greatly appreciated.

Thanks again!

 

[My Aswer Is This]

There should be someway to associate the validation list selection with the copy to range.
otherwise you will have to make 16 case statements

Can we associate the way the names are listed in the validation list with the range to copy too.

I'm not sure how to get the index number of the value selected from the validation list.
Do you know how to do that Michael?

 

[tonyyy]

I'm not sure how to get the index number of the value selected from the validation list.

@MAIT,

You might try the following...

Sub GetValidationListIndex()
    Dim inputRange As Range
    Dim i As Double
    Set inputRange = Evaluate(Range("C9").Validation.Formula1)

    i = Application.WorksheetFunction.Match(Range("C9"), inputRange, 0)
    Debug.Print i
End Sub

Cheers,

tonyyy

 

[My Aswer Is This]

Tony. Thanks for your reply.
I'm getting an error on this line:

Set inputRange = Evaluate(Range("C9").Validation.Formula1)

The error says: "Run time error 424 Object Required

I do have a validation list in Range(C9)

 

[tonyyy]

@MAIT,

When creating the Data Validation, the Source field needs to be a range reference, eg, =$O$5:$O$10.

If you manually type a comma separated list the macro will generate an Object Required error.