Variable sheetname in formula

[KongUK]

Hi

I am getting variable update required message when trying to pass activecell.value into a string variable to specify the sheet

Basically I have a list of sheetnames in A:A and want to add a countif formula to D:D that counts YES in therespective sheetname for H:H

I can loop down the list no problem but get stuck with how to put the sheet name in


Dim sht As String
With ActiveCell.Offset(0, 3)

sht = ActiveCell.Value
.Formula = "=Countif(sht!H:H,""Yes"")"
End With

It just puts sht! into the formula instead of the cell name of the sheet?

Any help much appreciated, thanks

[JKwan]

try this:

.Formula = "=Countif(" & sht & "!H:H,""Yes"")"

[KongUK]

Ok, Im sure I tried that but it's working now

Also I changed

sht = ActiveCell.Value

to


sht = "'" & ActiveCell.Value & "'"

Thank you