How to display filtered Pivot table data on separate sheets?

[eeigor]

Actually the question is asked in the topic. An example of a solution in Excel is here.

[Villeroy]

A grouped Base report does something like that with a nicer layout.

[Villeroy]

Here we go again.
------------
Select the source range including the column labels, call Data>Define Range... and give a name to the new database range. Make sure that the "column labels" option is checked under "More Options". A database range is a different kind of named range.
------------
File>New>Database...
Connect to existing database
Type: Spreadheet
Specify the spreadsheet file
Save the database document.
The Base document is connected to the spreadsheet document. Nothing has been copied, imported, converted. Your data are stored in the spreadsheet, not in the database document.
In the tables section you should see the name of the database range and when you open it, it looks like a database table.
Close it again, Right-click the table icon and call "Report Wizard".
Start with a simple tabular report, grouped by whatever category you wanted to split into separate sheets.
In the last step of the wizard choose "Edit report". It is not yet finished.
Click the blue group box and choose "Force new page: Before Section" in the properties dialog (View>Properties...).
Save and close the report design.
Save the containing database.
-----------------------------
Open the report. It is like a read-only text document generated from the database data (coming from a spreadsheet in this particular case).
If you grouped by "Person Name" it looks like:

Charly Chaplin
< table data >
----------- page break ----------
Buster Keaton
< table data >
----------- page break ----------
Stan Laurel
< table data >
----------- page break ----------

If you are not satisfied by the layout, just let the wizard create another one. Manual report design is a little bit tricky.

[eeigor]

@Villeroy, thank you. It will take me a while to figure out this solution.

[Villeroy]

I overlooked an important thing: You want to split the pivot table. My solution splits the source data of the pivot table. If your data were stored in a database, you could create the report from an aggregation query (a pivot is the same as an aggregation query). Base can not do aggregation queries with pseudo-databases that are lilnked to sheets.

[eeigor]

Everything worked out. A rare task. Otherwise, it might be worth writing a macro. Moreover, in my particular case, the pages had to be saved in separate files and sent out.
By the way, I first met the Base. I once knew MS Access very well.

[Villeroy]

I have written such a macro for Base reports. It changes a criteria value, runs a report, saves the report as separate Writer document,, changes criteria value, runs report, ...
viewtopic.php?f=100&t=94514

[Villeroy]

Everything worked out. A rare task. Otherwise, it might be worth writing a macro. Moreover, in my particular case, the pages had to be saved in separate files and sent out.

OK, this may become an interesting new feature. The attached extension installs a Basic library "PivotSplitter". Before calling the Main routine, click the grey pivot cell which shows the name of the field to be grouped. This can be a page field, a column field or a row field.
The macro detects the pivot around the active cell, detects the field specified by the active cell, creates a subdirectory "PivotExport", hides all field items, unhides them one by one and copies for each one the entire sheet to a new spreadsheet document and saves it to the subdirectory under the item's name. Finally, it shows a message about how many files have been stored to which subdirectory.
Configuration: If you set cDirName on top of the module to "", the macro creates a timestamped subdirectory for each run.

The macro works somehow, BUT
Unhandled problems:
0) Ugly Basic code with a dispatch macro as workaround because I don't understand interface XTransferable.
1) Invalid file name characters in field item names.
2) Does someone know how to not add a new file to the recent docs list?
3) There are "special pivot items" with empty names and in case of numeric groups there are items named like "<0" or ">100". The macro does not handle them in any special way.
4) If you do not see the column field in question but a column field named "Data" representing the data fields, drag "Data" to row field position and the name of your column field will appear.
Pivot macros are a can of worms.