palaeontology
Active Member
- Joined
- May 12, 2017
- Messages
- 444
- Office Version
- 2016
- Platform
- Windows
I have a code that relates to cells in a sheet called "3 ... DECISION MATRIX"
I used to have a button in that sheet that ran that macro, but I now have a need to run that same macro using a button in a different sheet called "3 ... TIE BREAKER MATRIX"
How do I amend the code so that it understands I'm referring to the worksheet "3 ... DECISION MATRIX" ???
Very kind regards,
Chris
VBA Code:
Sub All2Combos_v3()
Range("BH35:CG167").Select
Range("CG35").Activate
ActiveWindow.SmallScroll Down:=-132
Selection.ClearContents
Dim a As Variant, b As Variant
Dim i As Long, j As Long, x As Long, y As Long, col As Long, lr As Long
For col = 60 To 85 '<- Columns Bh to Cg
lr = Cells(35, col).End(xlUp).Row
If lr > 5 Then
a = Range(Cells(6, col), Cells(lr, col)).Value
ReDim b(1 To UBound(a))
x = 0
For i = 1 To UBound(a)
If Len(a(i, 1)) > 0 Then
x = x + 1: b(x) = a(i, 1)
End If
Next i
If x > 1 Then
ReDim a(1 To WorksheetFunction.Combin(x, 2), 1 To 1)
y = 0
For i = 1 To x - 1
For j = i + 1 To x
y = y + 1: a(y, 1) = b(i) & b(j)
Next j
Next i
Cells(35, col).Resize(y).Value = a
End If
End If
Next col
End Sub
I used to have a button in that sheet that ran that macro, but I now have a need to run that same macro using a button in a different sheet called "3 ... TIE BREAKER MATRIX"
How do I amend the code so that it understands I'm referring to the worksheet "3 ... DECISION MATRIX" ???
Very kind regards,
Chris