Copy value from one of a number of cell depending on which contains value

[jameswilliammitchell]

I have a worksheet in which the user will enter data into one of 5 cells depending on their input data. I need a code to attach to a command button that will copy the value from the cell which was used to another sheet.
The user will enter data in either C7, C8, C9, C10 or F39.

So I need to command button to copy which ever of these cells contains a value to another worksheet.
Any help would be appreciated.
Thanks

 

[VoG]

Try like this: change the part in red to suit

Private Sub CommandButton1_Click()
Dim c As Range
For Each c In Range("C7,C8,C9,C10,F39")
    If c.Value <> "" Then
        c.Copy Destination:=Sheets("Sheet2").Range("A1")
        Exit For
    End If
Next c
End Sub

 

[jameswilliammitchell]

Thank you, this works with the command button being on Sheet 1 but I wanted to have the command button on the sheet in which the value is finally copied to.

Also I forgot to mention that cell F39 is on a seperate sheet.

So I have C7,C8,C9 and C10 on sheet 1.
F39 on sheet 2 and I want to copy which ever has a value in it to cell B21 on sheet 3 using a command button which will be located on sheet 3. How could i change the code to do this?

Thank You

 

[VoG]

Try

Private Sub CommandButton1_Click()
Dim c As Range
For Each c In Sheets("Sheet1").Range("C7,C8,C9,C10")
    If c.Value <> "" Then
        c.Copy Destination:=Range("B21")
        Exit Sub
    End If
Next c
Sheets("Sheet2").Range("F39").Copy Destination:=Range("B21")
End Sub

 

[jameswilliammitchell]

Thanks works perfectly