Thank you so much for your help earlier. I decided in the meantime to try and do an export template whereby the data was exported from my table into excel and then on Sheet2 I built a pivot table based off of the data.
In automation I tried to utilize the export of the same table to the same .xlsx filename and just had it go to Excel and point to the data worksheet, begin at column a(1) and clear the prior worksheet data (rows will often change depending on the day).
The export completes successfully and the correct worksheet is updated with new data, however on Sheet 2 the pivot table is reduced to simple text and is no longer functioning as a pivot. Has anyone else seen this occur? I would have expected the pivot table to have just updated with the new source information since it was pointed to the same sheet and range.