Associate a Module to a worksheet

Question

I have this Module to run some events in Worksheet B. All Range used in this module refers to those cells in Worksheet B.

However, I would like to place the Button to run this module in Worksheet A. Is there a single-line header code or something to add so that all the Range will always refer to those in Worksheet B. Understand that alternatively, I could add 'Sheets(B).Range(#)' to every Range, however, this doesn't look Neat.

Below is the snippet of my Code. Thanks for your help.

Sub X_Iterate_Member()
Application.ScreenUpdating = False
On Error GoTo Error

Dim i As Integer, X As Integer

'------------------------Pre-guess X_value to be 0.5h-------------
For i = 4 To 11
    Range("B" & i) = Range("E" & i).Value * 0.5
Next i


'------------------------Iteration Loop---------------------------
i = 4 'Reset i to be 4-th Row

Do While i < 11 ' Working on Row 4 to 11

    Do While i < 11
        If Range("B" & i) <> "" And Range("J" & i) <> 0 Then Exit Do
    i = i + 1
    Loop

If Range("B" & i) = "" Or Range("J" & i) = 0 Then GoTo Increment
Range("Q" & i).GoalSeek Goal:=0, ChangingCell:=Range("B" & i)

Answer 1

Your code doesn't specify any worksheet. Therefore it acts on the ActiveSheet. If you want it to act on Sheet A you must first activate Sheet A. You might do that by calling a dedicated little procedure that first changes the sheet and then calls your existing sub unchanged.

Sub CallXiterateMember()
    ThisWorkbook.Worksheets("Sheet1").Activate
    X_Iterate_Member
End Sub

Any more elegant solution would include specifying a sheet in your existing procedure. The easiest way is to use a With statement and then just add a period before every reference to it. Since all your references appear to be Range, which should become .Range, you could achieve that using Find & Replace. Here is an example of what your code would look like.

Sub X_Iterate_Member()

    Dim i As Long, X As Integer

    Application.ScreenUpdating = False
    On Error GoTo ErrExit

    With Ws
        '------------------------Pre-guess X_value to be 0.5h-------------
        For i = 4 To 11
            .Range("B" & i) = .Range("E" & i).Value * 0.5
        Next i


        '------------------------Iteration Loop---------------------------
        i = 4 'Reset i to be 4-th Row

        Do While i < 11 ' Working on Row 4 to 11

            Do While i < 11
                If .Range("B" & i) <> "" And .Range("J" & i) <> 0 Then Exit Do
                i = i + 1
            Loop

        If .Range("B" & i) = "" Or .Range("J" & i) = 0 Then GoTo Increment
        .Range("Q" & i).GoalSeek Goal:=0, ChangingCell:=.Range("B" & i)

Increment:

    End With

    Exit Sub
ErrExit:
End Sub

If you want to specify the variable Ws within your procedure you would have to add

Dim Ws As Worksheet
Set Ws = ThisWorkbook.Worksheets("SheetA")

and then change the worksheet's name before you run the code. More flexible would be if you were to pass Ws as an argument in the procedure call, like

Sub X_Iterate_Member(Ws As Worksheet)

Now you can call your function with the sheet name of your choice, for example as shown below.

Sub CallXiterateMember()

    Dim WsName As String

    WsName = Worksheets("Sheet1").Cells(3, "A").Value
    X_Iterate_Member ThisWorkbook.Worksheets(WsName)
End Sub

I imagined you would have a Data Validation drop-down in Sheet1!A3 where you select a sheet name. The you press a button to call CallXiterateMember. This sub would then read the sheet name from the worksheet and pass it to your modified procedure for processing.