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 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