How to set up vba code for only one sheet?

Question

I am currently stuck trying to build a button that will run a sumifs macro. I am trying to build the button in Sheet1 and have the sumifs execute on Sheet2. What I currently have is:

Option Explicit

Sub Sumifs()
    Dim Sht2 As Worksheet
    Dim EndRow As Long
    Dim i As Integer
    Dim SumRange As Range
    Dim CrtA As Range
    Dim CrtB As Range

    With Sht1
        EndRow = Cells(Rows.Count, "A").End(xlUp).Row
    End With

    Set Sht2 = Worksheets("Sheet2")
    Set SumRange = Worksheets("Sheet3").Range("L5:L10")
    Set CrtA = Worksheets("Sheet3").Range("C5:C10")
    Set CrtB = Worksheets("Sheet3").Range("K5:K10")

    For i = 5 To EndRow
        sht2.Cells(i, 4) = WorksheetFunction.SumIfs(SumRange, crtA, Range("G" & i), crtB, Range("B" & i))
    Next i
End Sub

I have tried running it with alt + F8 and it works great as long as I am in Sheet2, if I try running it on Sheet1 nothing happens.

Also, is there a way to link the sumifs criteria to a separate worksheet? Specifically, I am trying to have Range ("B" & 1) be linked to a cells J5:J10 on Sheet3. Currently, I get a type error whenever I try

worksheets("sheet3").range ("B" & 1)

Really appreciate any advice you can provide. Thanks

Answer 1

Put the code in a module:

Option Explicit

Sub SumIfS()
    Dim Sht1 As Worksheet
    Dim Sht2 As Worksheet
    Dim EndRow As Long
    Dim i As Integer
    Dim SumRange As Range
    Dim CrtA As Range
    Dim CrtB As Range

    Set Sht2 = Worksheets(2)
    Set Sht1 = Worksheets(1)

    With Sht1
        EndRow = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With

    Set SumRange = Worksheets("Sheet3").Range("L5:L10")
    Set CrtA = Worksheets("Sheet3").Range("C5:C10")
    Set CrtB = Worksheets("Sheet3").Range("K5:K10")

    For i = 5 To EndRow
        With Sht2
            .Cells(i, 4) = WorksheetFunction.SumIfS(SumRange, CrtA, .Range("G" & i), CrtB, .Range("B" & i))
        End With
    Next i

End Sub

The idea is that it is really a good practice to mention the "Parent-Worksheet" of the range. Otherwise it is taking either the ActiveSheet (if in a module) or the worksheet in which the code resides.

Whenever you are using construction like this:

With Sht1
    EndRow = Cells(Rows.Count, "A").End(xlUp).Row
End With

You need to tell VBA to refer somehow to the Sht1. This is done, through dots here:

Otherwise it takes the Parent worksheet, which is either ActiveSheet or the one where the code is (if not in a module).