Macro to pull specific data from one worksheet to another worksheet

Hello. I'm new to vba/macros. So new that I can't actually do what I want this macro to do. I'm hoping you can help.

Basically, I'm looking for a macro that will count specific data in one worksheet and write that total to a specific column in another worksheet.

I've attached the specific file on which the macro would run. I know how to do what I want using excel functions, but as I have hundreds of these files, using excel functions would take quite a long time. But I'm going to include the excel function below to better explain the problem.

Ok, so:

I've already found a macro that will write the worksheet names into a column, which is Column A in the Summary Worksheet.
The excel function for Column B would be =COUNTIFS('UD 2023'!D:D,"needs spacer",'UD 2023'!C:C,">=1"), with the function changing to include the worksheet name in the respective row
The excel function for Column C would be =COUNTIFS('UD 2023'!D:D,"needs spacer",'UD 2023'!C:C,""), with the function changing to include the worksheet name in the respective row
The excel function for Column D would be =COUNTIFS('UD 2023'!B:B,"yes"), with the function changing to include the worksheet name in the respective row


And what would be even more amazing is if the macro wrote the total to a completely different file. So I could have one file that is The Summary of these hundreds of files (but that would be asking too much, no?)

Thanks in advance!

There are multiple flaws in your functions. One I will correct, one I will not (maybe you meant it to occur). You cannot utilize the countif function on multiple ranges like you have. I corrected it by using the sum function combined with 2 countif functions.

Your column C function (when used correctly)

=COUNTIFS('UD 2023'!D:D,"needs spacer",'UD 2023'!C:C,"")

Will count all blank cells in column C for the whole entire worksheet. I think this is a flaw but I could be wrong.

Try:

Sub PickMe()
Dim sumsht As Worksheet:    Set sumsht = Sheets("Summary")
Dim ws As Worksheet
Dim LR As Long

Application.ScreenUpdating = False
For Each ws In Worksheets
    LR = sumsht.Range("A" & Rows.Count).End(xlUp).Row
    If Not ws.Name = "Summary" Then
        sumsht.Range("A" & LR + 1).Value = ws.Name
        sumsht.Range("B" & LR + 1).Formula = _
            "=SUM(COUNTIF('" & ws.Name & "'!D:D, ""needs spacer""), COUNTIF('" & ws.Name & "'!C:C, "">=1""))"
        sumsht.Range("C" & LR + 1).Formula = _
            "=SUM(COUNTIF('" & ws.Name & "'!D:D, ""needs spacer""), COUNTIF('" & ws.Name & "'!C:C, """"))"
        sumsht.Range("D" & LR + 1).Formula = _
            "=COUNTIFS('" & ws.Name & "'!B:B, ""yes"")"
    End If
Next ws
Application.ScreenUpdating = True

End Sub

Thank you!!! This darn close to what I need, and with a tweaking, I think I can get it. Thanks again!