VBA - Variable sheet name in sumif formula

Question

I'm trying to use SUMIF formula in VBA, based on a sheet that can have diferent names.
When I'm on this ActiveSheet (can vary), I want to add another sheet template from another workbook Template_test and put a SUMIF formula referenced to the activesheet.
When I run the macro, error occurs (1004) and stops at the formula.

This is what I have:

Sub test()
    Set CurBook = ThisWorkbook
    Dim wksheet As Worksheet
    Set wksheet = ActiveSheet

    MsgBox CurBook.Name & "_" & wksheet.Name

'Open template
    Workbooks.Open filename:= _
        "D:\Template_test.xlsm"

'Copy new sheet
    Sheets("template").Select
    Sheets("template").Copy After:=CurBook.ActiveSheet

'Close Template file
    Windows("Template_test.xlsm").Activate
    ActiveWindow.Close

 'SUMIF Formula in Template regarding wksheet in CurBook
    Range("E11").Select
    ActiveCell.FormulaR1C1 = _
        "=SUMIF('& wksheet &!C2,""=P-SEC"",'& wksheet &!C16)" End Sub

Is there a way to solve this and make it work?

OpiesDad OpiesDad · Accepted Answer · 2015-03-13T19:06:23

I see two issues here:

i) wksheet is an object of type 'WorkSheet' and not a string, so you can't use it as a string.

ii) wksheet is available in the code, but not in the worksheet, so when you paste the function into the cell, you need to exit the string and append the name.

Try this:

ActiveCell.FormulaR1C1 = _
    "=SUMIF('" & wksheet.name & "'!C2,""=P-SEC""," & wksheet.name & "!C16)"

End Sub

Note: If the sheet name has a space in it, you need to surround it in apostrophes in the formula otherwise it will cause an error.

VBA - Variable sheet name in sumif formula

1 Answers