I confess I couldn’t get this method to work. It’s my limitation, not the software 's. In the end, I did a copy and paste of each tab onto a single tab and ran the pivot table from there. Since most tabs were historical and don’t change, I just have to update the aggregated data with latest additions. Not ideal but it gets the results.
Thanks for trying to help!