Button linking to another cell and sheet

**SoutherBoy718** · 01-06-2007, 08:21 PM

What I'm trying to build here is an automatic drawing simulation instead of giving out tickets and drawing them from a hat or drum.

Setup: I have 2 worksheets where Sheet1 contains a command button and will display results starting on row 15 (A15) while incrementing by 1 (A16) each time the command button is hit. The data which should be shown on these rows (A15, A16, etc) will be derived from Sheet2 which has one column of names (A1 thru 'end of names').

So far, I've been able to "randomize" the names on Sheet2 to receive 1 result (or pick) by using this formula and hitting F9: =INDEX(A:A,RAND()*COUNTA(A:A)+1)

My Questions:
1 - How can make the command button on Sheet1 perform the function of hitting F9 on Sheet2 and receive the result starting on row 15 - Sheet1?

2 - Instead of just receiving data/value from 1 column, is it possible to receive a row of data/values from let say, 4 columns (Sheet2 - A25 thru D25), and have this be populated starting on row15 - Sheet1?

Is this possible?

Please let me know if more details are needed. Many "thanks" in advance for your time and effort on this.

**MSP77079** · 01-06-2007, 09:22 PM

How can make the command button on Sheet1 perform the function of hitting F9 on Sheet2 and receive the result starting on row 15 - Sheet1?

Private Sub CommandButton1_Click()
    'simulate pressing F9 for Sheet2
    Sheet2.Calculate
End Sub

Note this code is using the CodeName for Sheet2, which might not agree with the "tab" name for sheet2.

is it possible to receive a row of data/values from let say, 4 columns (Sheet2 - A25 thru D25), and have this be populated starting on row15 - Sheet1?

Private Sub CommandButton1_Click()
    Sheet2.Calculate
    Sheet2.Range("A25:D25").Copy
    Me.Range("A15").PasteSpecial Paste:=xlPasteValues
End Sub

**SoutherBoy718** · 01-06-2007, 10:05 PM

Thanks you very much for the reply.

I do have another question in which I didn't ask before....

In the situation above, how would you randomize 4 columns of data (or multiple rows) from Sheet2 when hitting the command button on Sheet1? And, ofcourse returning the value to row15....then on the next hit, row16, and so on?

Here's an example of 2 rows of data from Sheet2....

........ A ......... B ........ C ............. D ........
1..... John ..... Doe ... AnyCity .... AnyState
2..... Jane ..... Due .. SomeCity .. SomeState

Sorry for not asking this the first round and thanks for your help.

**MSP77079** · 01-06-2007, 10:26 PM

This is how I would do it. Just for demonstration, I put the data in a different column than I took it from. And, I delete the columns I write to, so be careful. Save a back up copy before testing (always a good idea).

Sub randomizeSheet1()
Dim ws As Worksheet
Dim allData() As String
Dim tempData As String
Dim xChangeWith As Long

    Randomize

    Set ws = Sheet1
    ws.Columns("F:J").Delete
    'determine last Row on sheet1
    lastRow = ws.Cells(ws.Rows.Count, 1).End(xlUp).Row

ReDim allData(lastRow, 4)
    
    'read in the data
    For i = 15 To lastRow
        For j = 1 To 4
            allData(i, j) = ws.Cells(i, j)
        Next j
    Next i
    
    'randomize the data (shuffle the deck)
    For i = 15 To lastRow
        xChangeWith = Int(((lastRow - 15) * Rnd) + 15)  ' Generate random value between 15 and LastRow
        ws.Cells(i, 6) = xChangeWith
        For j = 1 To 4
            tempData = allData(xChangeWith, j)
            allData(xChangeWith, j) = allData(i, j)
            allData(i, j) = tempData
        Next j
    Next i
    
    'write the shuffled data
    For i = 15 To lastRow
        For j = 1 To 4
            ws.Cells(i, j + 6) = allData(i, j)
        Next j
    Next i

End Sub

**MSP77079** · 01-07-2007, 12:44 AM

Here is code to do both. It does not randomize the data, it merely pull a random row each time ...

Sub pullRandomRow()
Dim ws1 As Worksheet
Dim ws2 As Worksheet
Dim lastRow As Long
Dim nextRow As Long
Dim rndRow As Long
Dim rng As Range

    Randomize
    Set ws1 = Sheet1
    Set ws2 = Sheet2
    
    'last row on worksheet2
    lastRow = ws2.Cells(ws2.Rows.Count, 1).End(xlUp).Row
    'pick a random row
    rndRow = Int(((lastRow) * Rnd) + 1)  ' Generate random value between 1 and LastRow
    
    'move this row to next available row on worksheet1
    nextRow = ws1.Cells(ws1.Rows.Count, 1).End(xlUp).Row + 1
    If nextRow < 15 Then nextRow = 15
    
    Set rng = Range(ws2.Cells(rndRow, 1), ws2.Cells(rndRow, 4))
    rng.Copy Destination:=ws1.Range("A" & nextRow)
    ws1.Range("F" & nextRow) = rndRow
    
    'delete the row so we do not bring it over again
    ws2.Rows(rndRow).Delete

End Sub