[Calc, Basic] Copy visible cells

Shared Libraries
Forum rules
For sharing working examples of macros / scripts. These can be in any script language supported by OpenOffice.org [Basic, Python, Netbean] or as source code files in Java or C# even - but requires the actual source code listing. This section is not for asking questions about writing your own macros.
Post Reply
User avatar
Villeroy
Volunteer
Posts: 31269
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

[Calc, Basic] Copy visible cells

Post by Villeroy »

2018-11-23: I made the code more modular. Now we can copy values only, values and formulas or a full copy&paste
Copies the visible cells from a selected rectangle of cells into a merged target range skipping filtered rows and hidden rows and columns as well. The built-in copy&paste operation skips only filtered rows.
The demo document has two source sheets with hidden/filtered cells and one target sheet with all cells visible. Play with it.
Import or copy the library "Calc" to the global location "My Macros" and use one of the routines copyValues, copyFormatAndValues, copyFormatAndFormulas.

Note on formulas: Copying formulas may be dangerous because relative references may refer to wrong cells. As a demo you may hide column D of the first example sheet and call copyFormatAndFormulas. The formulas in column E refer to the left neighbour cells in column D but in the target sheet left neighbours won't be the text values of the cells that used to be in column C.
Attachments
copyVisibles.ods
Copy visible cells with formatting, without shapes, with or without formulas.
(73.46 KiB) Downloaded 589 times
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Post Reply