0

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?

L42
  • 19,427
  • 11
  • 44
  • 68
sekiumo
  • 3
  • 1
  • 2

1 Answers1

2

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.

OpiesDad
  • 3,385
  • 2
  • 16
  • 31
  • Hi, tried with that change. Got the same error. Tried to change the place of the " and & and get the same error always. – sekiumo Mar 16 '15 at 17:12
  • Try setting the formula equal to a variable so, testvar = ""=SUMIF(" & wksheet.name & "!C2,=""P-SEC""," & wksheet.name & "!C16)" and then debug.print the variable and manually put the value into a cell to see what happens. – OpiesDad Mar 16 '15 at 17:53
  • Tried Debug.Print with the fiist formula you provided, got FALSE in Immediate. Tried to put the variable testvar, but it shows a Compile error in the ! before C2. – sekiumo Mar 18 '15 at 11:12
  • Should have only been one quote at the beginning. My mistake. Try testvar = "=SUMIF(" & wksheet.name & "!C2,=""P-SEC""," & wksheet.name & "!C16)" – OpiesDad Mar 18 '15 at 13:44
  • Tried with that and got the same response in Immediate window: False. – sekiumo Mar 18 '15 at 15:58
  • You need to do the lines "testvar = ....." and then hit enter and then do "Debug.print testvar" to get the output in the immediate window. What you've tried has output whether testvar is already equal to this, which is False. – OpiesDad Mar 18 '15 at 17:42
  • Ok understood. Got this: =SUMIF(Sheet 1!C2,="P-SEC",Sheet 1!C16) – sekiumo Mar 19 '15 at 14:57
  • And there's the problem. Your sheet name has a space in it. For this, you need to change the formula per the edit above. – OpiesDad Mar 19 '15 at 17:40
  • Thanks, that's it! Solved ;) Just one thing, the equal in P-SEC needs to be inside: ,""=P-SEC"" – sekiumo Mar 19 '15 at 18:50