How to use Visual basic editor to Create a new sheet and insert text & formula

[DavidW2022]

I am looking for a command that can add a new sheet within Excel. Which I have:

Sheets.Add(After:=Sheets("Combined")).Name = "Metrics"

But after this I am wanting to add text shown below automatically. Column A is just names and column B is counting occurances on the first sheet "=COUNTIF(Combined!$F:$F,"*"&A1&"*")"

Mitzi	0​
DAN	4​
TW	1​
AA	11​
Comps	1​
Holds	0​
kh	1​
DW	0​
RL	1​
SPH	0​
TimJ	0​
LP	6​
TH	0​
Total	25​

Is there a way I can automate this all into one command line?

 

[DanteAmor]

Is there a way I can automate this all into one command line?

I don't think it's possible on a single line, but try the following:

Sub CreateSheet()
  Dim sh As Worksheet
  Dim arr As Variant
  
  Application.DisplayAlerts = False
  Application.ScreenUpdating = False
  On Error Resume Next
    Sheets("Metrics").Delete
  On Error GoTo 0
  
  Set sh = Sheets.Add(after:=Sheets("Combined"))
  sh.Name = "Metrics"
  arr = Array("Mitzi", "DAN", "TW", "AA", "Comps", "Holds", "kh", "DW", "RL", "SPH", "TimJ", "LP", "TH")
  sh.Range("A1").Resize(UBound(arr) + 1).Value = Application.Transpose(arr)
  sh.Range("B1:B" & UBound(arr) + 1).Formula = "=COUNTIF(Combined!$F:$F,""*"" & A1 & ""*"")"
End Sub