Countif result in variable

Try something like this.

   Dim x As Long
   Dim y As Long
   Dim i As Long
      
    x = Application.WorksheetFunction.CountIf(Sheets("Milestones and duration").Range("F:F"), "yes")
    y = Application.WorksheetFunction.CountA(Sheets("Partners").Range("A:A"))

Your copy\paste loop isn't quite right, but I don't follow what you want done.