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!