VBA Macro for Pivot Table with Formulas

Hello Gurus of Excel,

I'm currently trying to find a method to create a pivot table by using VBA. I tried previously attempts but it unfortunately did not work. I have alot of data over many workbooks that requires the same formula but the data sizes vary from time to time. I provided an example of the excel file. On the pivot tab I have created the desired goal for creating this. Any help provided would be greatly appreciated!

Note: On the Pivot tab it has formulas that was created based on the raw data so I hoping to replicate that with a macro

Thanks in advance!