How to create a button to run formula on every sheet in workbook
Category: [Excel] Demo Available
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.
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.
See also: Index to Excel VBA Code and Index to Excel Freebies and Lesson 1 - Excel Fundamentals and Index to how to… providing a range of solutions and Index to new resources and reference sheets
See also:
| How to create and auto run macro if value on cell A1 is less than value on B2 |
| How to run a macro if a column has a certain text in it |
| How to run code when cell value changes from empty to entered value |
| How to copy the entire sheet and paste as values - running on multiple tabs |
Click here to visit our Free 24/7 Excel/VBA Help Forum where there are thousands of posts you can get information from, or you can join the Forum and post your own questions.