ExcelBanter - Worksheet name as a variable in VBA

ExcelBanter (https://www.excelbanter.com/)

- Excel Discussion (Misc queries) (https://www.excelbanter.com/excel-discussion-misc-queries/)

- - Worksheet name as a variable in VBA (https://www.excelbanter.com/excel-discussion-misc-queries/446580-worksheet-name-variable-vba.html)

Worksheet name as a variable in VBA

Hello,

I am trying to use a cell value (C2 in the worksheet "Test") to dictate the worksheet I want to reference from in my if formula. In other words, all the '21-May' in my formula should be replaced by the cell value (C2) that refers to another worksheet. I replaced '21-May' with strName but it did not work. Will appreciate any help. Thank you.

Dim strName As String
strName = ActiveWorkbook.Sheets("Test").Range("C2").Value
Range("B36").Select
ActiveCell.FormulaR1C1 = _
"=IF(AND(R[-4]C[34]=0,R[-4]C[36]=0),0,(SUMIFS('21-May'!C21,'21-May'!C8,""CSA"",'21-May'!C9,""Late"")+SUMIFS('21-May'!C21,'21-May'!C8,""CSA"",'21-May'!C9,""Exception""))/(R[-4]C[34]+R[-4]C[36]))"

Worksheet name as a variable in VBA

When you replaced '21-May' with strName, did you include the apostrophes? In a formula the '21-May' is a reference to a tab, and the value in cell c2 likely does not include apostrophes. Excel rejects the formula written by VBA if the apostrophes are not present.