Data Pulling

I'm trying to figure out the best way to pull data together from multiple tabs within an excel file. The file is made so that it can track issues within reports that are discovered. I'll try to break it down the best I can to explain what I have and what I'm trying to do.

• Within the file there are 5 tabs that are considered locations and they are named: LX, LV, CR, HR, FL
  • Each tab is set up the same way with the only real important information in these columns of a table
    • Column A - PCR Date
    • Column C - PCR Owner
    • Column G, I, K, M - Modification Category (1-4) these fields are actually drop down options from the Category Master List 

• Within the file there is a 6th tab that has at able in it that is called Category Master List.
  • Column A - Options for the dropdown in the above tab Modification Category columns
  • Column B - Breakdown into one of three main categories (Billing, Compliance, Quality)

Now this is what I'm trying to do:

• Fiscal Year Data (September 2025-August 2026) - I need to see how many times each of the dropdown options within the Master List show up in column G, I, K, M for each of the 5 location tabs. 
• Monthly Data - I need to see how many times each of the dropdown options within the Master List show up in column G, I, K, M for each of the 5 location tabs along with how many times there is an entry with a date within that month.

I would like to try and figure out how to put everything into one data table/chart type thing so it's easier to see and compare. Currently, everything needed is across a few different pivot tables and I would really like the data to pull itself automatically instead of me having to go in and copy all the data from one spot into another just to get the pivot results. Below is what I'm having to look at currently.