Excel Pivot Table Macro Active Sheet as Data Source

[ExcelNoobee]

Hello,

I am struggling to figure out how to create a macro that allows me to create pivot tables using the current/active sheet as the data source. Each sheet has the same format and layout. My goal is to create a report sheet that will have a combination of all of the pivot tables.

Thank you so much in advance!
Excel Noobee