I am working with a workbook with the sheets (Data & Pivot). Pivot sheet contains a Pivot table with data source from Data sheet.
We're using xlwings to write data to a new workbook with worksheet as Data. As a second step, I want to copy the Pivot Table as the second worksheet that references data in the new worksheet. However, when I copy it over, it keeps the data source as [Workbook1.xlsx]Data'!$A:$Y
I'd like to be copied over with data range as Data!$A:$Y as in the original source workbook.
Using below code to copy the worksheet:
source_wb.sheets['Pivot'].copy(after=new_wb.sheets['Data'], name='Pivot')
One of the reason I don't want to add an additional step to correct the data source range is because worksheets may or may not contain pivots. I have read documentations for xlwings and open items if any, but no luck yet.