run code from different sheet

[palaeontology]

I have a code that relates to cells in a sheet called "3 ... DECISION MATRIX"

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

 

[maabadi]

You can input sheet index number within inputbox that apears when code run.
sheet index number = Sheet arrangement number at below of workbook. 1 for First Sheet, 2 for 2nd and ...
Try this:

Sub All2Combos_v3()
  Dim a As Variant, b As Variant, k As Long
  Dim i As Long, j As Long, x As Long, y As Long, col As Long, lr As Long
  On Error Resume Next
Resum1:
  k = Application.InputBox("Please Input Sheet Arrangement Number" & vbLf & "(1 for First Sheet, 2 for 2nd & ...)", "Sheet ArrangeMent Number", , , , , , 2)
If k = 0 Then Exit Sub
If Err.Number = 13 Or k > Sheets.Count Then
MsgBox "Please Input The Correct Sheet Number"
GoTo Resum1:
End If
On Error GoTo 0
With Sheets(k)

  .Range("BH35:CG167").ClearContents
 
  
  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 With
End Sub

 

[Peter_SSs]

You just need to state which sheet you want to apply it to and then qualify all cell/range references to that worksheet with a preceding "."
(@maabadi you appear to have missed one )
There should also be no need for the 'selection at the top to clear the cells.

Sub All2Combos_v3()
  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
 
  With Sheets("3 ... DECISION MATRIX")
    .Range("BH35:CG167").ClearContents
    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 With
End Sub

 

[palaeontology]

Thankyou to both of you.

That works brilliantly, as always.

Thankyou very much, again,

kind regards,

Chris

 

[maabadi]

You're Welcome & Thanks for follow-up.