Duplicate a control button

[Rickinnocal]

Hello, everyone.
I have a worksheet that performs four-way interpolation on values taken from paper tables.

It has to do this for 40 sets of data. After the four data points are entered for the first tank and it produces the interpolated result, I created a Command Button to push the answer to a second sheet.

This works fine. However, I need 40 buttons to do the push for each tank (Actually 20 tanks, each has a 'Before' and 'After' value) I can just make 40 buttons, but is there an easier way to duplicate them without having to manually enter each macro code?

The code for the button I already have reads :

Private Sub cmdSendData1_Click()

Worksheets("ROB (Before)").Range("i10").Value = Worksheets("Interpolator").Range("e9").Value

End Sub

The code for the second button would need to read :

Private Sub cmdSendData2_Click()

Worksheets("ROB (Before)").Range("i11").Value = Worksheets("Interpolator").Range("e15").Value

End Sub

The row of the destination cell increases by 1 for each button, but the row of the source cell increases by 6.

Is there an easy-ish way to do this?

Thanks.

 

[diddi]

if this was a userform the answer is Yes, there is a very elegant way to do it, but i am not sure if it works on worksheet controls. it may if they are activeX controls but cant say for sure. it involves writing a class and assigning each commandbutton_click event to the class. having made this suggestion, there may be another member who is familiar with referencing classes from worksheets

 

[mikerickson]

You could make the 40 buttons with this code

Sub Macro1()
    Dim i As Long
    For i = 0 To 39
        ActiveSheet.Buttons.Add(380 + 5 * i, 190 + 20 * i, 120, 30).Select
        With Selection
            .Name = "butRow" & Format(i, "00")
           .Characters.Text = "to row " & (10 + i)
           .OnAction = "Button_Click"
        End With
    Next i
End Sub

Which will assign them all to this macro

Sub Button_Click()
    Dim index As Long
    index = Val(Right(Application.Caller, 2))

    Worksheets("ROB (Before)").range(10 + index, 9).Value = Worksheets("Interpolator").Cells(9 + 6 * index , 5).Value
End Sub

But I can't help thinking that there is an easier interface than 40 different buttons.

 

[diddi]

i agree, that the 40 cmdbuttons is a bit inelegant. but thx for the interesting solution. i dont manipulate worksheets, i build pure userform driven applications and hide excel, none the less i will stash your code in my snippits library just in case. out of interest are the buttons created activeX.
Hope the OP appreciates the cleverness of this solution

 

[Rickinnocal]

Thanks, everyone. I agree that the 40 buttons are inelegant, but I wanted to leave the destination cells able to be typed into directly without overwriting any interpolation formulae, as on many occasions the required result is a straight read out of the table.

 

[mikerickson]

...out of interest are the buttons created activeX.

No, the buttons in that code are from the Forms menu.