Create a Table from data set

Hello,

I compile a routine report from the data in the attached spreadsheet. I’m hoping to write some code to streamline this process.

In the attached workbook, there are three spreadsheets.

(1) In the first spreadsheet “Before” is the data I receive.

(2) In the “After” spreadsheet, I removed the Event/Date Time, Last Name, First Name columns, and totaled the number of times each FC4 code was counted in the “Before” spreadsheet. (I use 'subtotal' count, and then only leave the 'total' line for the subtotal, and delete other entries)

(3) I then use the “After" spreadsheet to create a Pivot Table (the final spreadsheet). That reports the City (Column A) as the Row Label, Count of FC4 (Column B) and Sum of Number (Column C).

I do not know if Pivot Tables can be commanded from VBA but if its, possible that would be ideal!
Any ideas to help streamline this process would be appreciated as going from the “Before” to “After” is quite a few steps (counting using subtotals, then removing rows that don’t include that count, then cutting out columns).

Thank you!