Sumif not working

[cvilla7174]

Hello , I am trying to do a sumif but the problem is what if i try to use the sheet code name it won't work, only if i use the sheet name, here is what i mean by that

sumifmacro Macro Range("E4").Select ActiveCell.FormulaR1C1="=SUMIF('ancma 4-7-2019'!R3C2:R173C2,april!RC9,'ancma 4-7-2019'!R3C5:R173C5)" Range("E5").Select End Sub

now if I use the same formula but trying to use the sheet codename it doesn't work why?

Sub mysumif()
Range("E4").Select
ActiveCell.FormulaR1C1 = "=sumif('sheet56'!R3C2:R173C2,sheet57!RC9,'sheet56'!R3C5:R173C5)"
Range("E5").Select
End Sub

now is there a way to use a variable that I can create to reference either the worksheet name or code name as variables?

[keebellah]

To reference a worksheet you can add a variable and set it to that worksheet

sumifmacro Macro'
'Dim ws   As Worksheet
Set ws = workshets("whatever name")  or use Set ws=Sheet1   
ws.Range("E4").ActiveCell.FormulaR1C1 = _
        "=SUMIF('ancma 4-7-2019'!R3C2:R173C2,april!RC9,'ancma 4-7-2019'!R3C5:R173C5)"
End Sub

DO NOT SELECT just refer to a range

[georgiboy]

Another way of looking at it might be:

Sub Test()    
    Dim tmpWS As Worksheet
    Dim tmpWS2 As Worksheet
    Dim CriteriaRng_ As Range
    Dim Criteria_ As String
    Dim SumRng_ As Range
    
    ' worksheet code name
    Set tmpWS = Sheet56
    Set tmpWS2 = Sheet57
    
    ' worksheet front end name
'    Set tmpWS = Sheets("Sheet name here")
'    Set tmpWS2 = Sheets("Sheet name here")
    
    With tmpWS
        Set CriteriaRng_ = .Range(.Cells(3, 2), .Cells(173, 2))
        Set Criteria_ = tmpWS2.Cells(3, 9).Value
        Set SumRng_ = .Range(.Cells(3, 5), .Cells(173, 5))
    End With
    
    ActiveCell = Application.SumIf(CriteriaRng_, Criteria_, SumRng)
    
End Sub

Hope this helps