Create Pivot Table via VBA - Any Source

Hello

Summary of what I need to do and why:

I have received a very large data set. I have pivoted that data set into a nice summary. Now, i have to send this off to managers who will drill into that data to see the breakdown of those summary lines - of which there can still be 10K+ lines in some cases.

There will be two drill down levels. the first is by the "Origin" of the sale. From there (the newly created table) i want to automatically create a PivotTable with Unit, Origin, Invoice, Sales Order down the rows, and Revenue and materials and Margin in the data section. I want this to be able to happen automatically no mater what the table name or sheet name and always on the most recently created sheet (from the drill-down) and always automatically without any buttons or commands. Since there are seven "Origins" this could happen seven times and I do not want it to get confused b/c the data table is always going to be different. The second level of drill down is just to choose the invoice that looks funny to them and see the lines of that invoice.

Not all mangers are excel savvy nor would they be able to create the second third or fourth pivot tables, so the automatic portion of this is a necessity in this case

thanks

i attached a sample of the data tables that i am working in