How to create a button to run formula on every sheet in workbook

Requirement:

The user has a workbook with multiple sheets that a given a unique worksheet name.

Each worksheet multiple columns of data. Each column on each worksheet has the same formatting and type of data. The number of rows in each worksheet varies. But each worksheet has the same number of colums.

The user has a formula that picks a random value from a specific column between row 2 and the last row of data. The user has it skipping row 1 from the column as that is the column header row.

The user wants to create a button to run this formula on each worksheet and put the results in 5 cells in a column that does not have data.

To give an example of the structure of the excel sheet:

Columns A - Z are used. Column E is the column the user wants to pull random values from. The user wants to automatically run the formula on each worksheet by pressing a button and have the results pasted in cells AA:2 = AA:6

The user can manually do this on each worksheet by pasting the formula in the cells that the user referenced, but there are 25 worksheets and it takes quite a bit of time to do this manually each week.

https://www.ozgrid.com/forum/forum/help-forums/excel-vba-macros/148884-create-button-to-run-formula-on-every-sheet-in-workbook

Solution:

Stopping duplicates using a formula can get very complicated. Try the attached, just click the button on Functions sheet.

Code assigned to the button (in Functions worksheet Object Module) is

Code:

Sub Button1_Click()
    RandomTickets
End Sub

and in a standard module

Code:

Sub RandomTickets()
    Dim x, i As Long, ii As Long, ws As Excel.Worksheet
    
    For Each ws In Sheets
        If ws.Name <> ActiveSheet.Name Then
            With ws
                x = .Cells(1).CurrentRegion.Columns(5)
                With CreateObject("system.collections.arraylist")
                    For i = 1 To 5
ReTry:
                        ii = Application.RandBetween(2, UBound(x, 1))
                        If Not .contains(x(ii, 1)) Then
                             .Add x(ii, 1)
                        Else
                            GoTo ReTry
                        End If
                    Next
                    ws.[i2:i6] = Application.Transpose(.toarray)
                End With
            End With
       End If
    Next
    MsgBox "5 random Tickets per employee successfully created.", , "Completed"
    
End Sub

Obtained from the OzGrid Help Forum.

Solution provided by KjBox.