Pivot table

having an Excel workbook with multiple sheets. each contain certain data and a pivot table in each one. recently started having problem that is when I copy a pivot table from an existing data sheet to a new inserted sheet and then changing the source of data to the new sheet and then refresh the pivot table then save and close the file; then when I try to open the file again I got an error and the most recent pivot table get deleted.  

tried several ways to solve the issue with no avail noting I'm using Microsoft 365 for Mac.

appreciate any help