Description:
This change assumes that the user intends to keep together source data and PT which occur on the same sheet. This is a safe assumption since the default for creating a PT is to place it on a new sheet - the user explicitly sets the destination to the current sheet or some other existing sheet when a PT is created. Current behavior breaks this assumption when a sheet is duplicated - PTs on the duplicate point to the original sheet.

This change makes LO-calc do what a naive user expects. Only an experienced user knows to manually edit source references so that PTs in the duplicate sheet use data in the duplicate sheet instead of the original sheet. The result is to make LO-Calc friendlier for new users and require less manual fiddling for experienced users.

The requested behaviour implies that the absolute sheet reference in the source of the PT is conditionally ignored during duplication. This is necessary because PT source and destination always use absolute sheet references. So this change logically adds an IF-THEN-ELSE condition while setting the source range of each Pivot Table on the duplicate sheet while a sheet is being duplicated. If a PT's source and destination are the same sheet, then when the sheet is duplicated, set the source of such PTs in the duplicate sheet to be the duplicate sheet instead of the original sheet. This logically mirrors the explicit change of the destination range of all PTs on the duplicate sheet.

IF compatibility with legacy LO-Calc is deemed necessary, then add a checkbox in Tools > Options > LO-Calc > Defaults (with 3 states: Uninitialized, No change, Duplication changes local source sheet reference), and add a dialog box which prompts the user for the choice to change PT source references to the new sheet on duplication. Dialog appears only if the default is uninitialized, letting the user set behaviour when the need first arises. Can be changed later in the options. My wording is ambiguous, feel free to improve it.

I'm biased by decades of LO-Calc usage, but in my opinion this change would be so much better in one tiny detail that this is really a bug fix.

Steps to Reproduce:
1. Create a small data table with titles as source for PT.
2. Create a PT with destination on the same sheet as the source.
3. Create a second PT with the same source but destination on a new sheet.
4. Duplicate the source sheet. The result is 3 sheets with 3 pivot tables.

Actual Results:
Source of the PT on the original sheet points to the original sheet.
Source of the second PT on its own sheet points to the original sheet.
Source of the PT on the duplicate sheet points to the original sheet.

Expected Results:
Source of the PT on the original sheet points to the original sheet. (unchanged)
Source of the second PT on its own sheet points to the original sheet. (unchanged)
Source of the PT on the duplicate sheet points to the duplicate sheet. (changed!)


Reproducible: Always


User Profile Reset: No

Additional Info:
Version: 25.2.3.2 (X86_64) / LibreOffice Community
Build ID: bbb074479178df812d175f709636b368952c2ce3
CPU threads: 16; OS: Linux 6.1; UI render: default; VCL: gtk3
Locale: en-CA (en_CA.UTF-8); UI: en-US
Calc: threaded