Macros for Calc can be created in two ways:
• By recording actions and letting OpenOffice create the macro for you
• By learning the complex Application Programming Interface and writing the macro yourself
Recording macros is much simpler then writing them. You don't need any programming experience, but there are some limitations on what you can do. However, many situations do not encounter any of them. All of the following important macros can be recorded by following the instructions given here in the right column. You can record all of them now, or you can record them as you need them.
How do you record a macro? As always, it is really best to read the documentation. In summary:
• Use Tools → Macros → Record Macro (a small window is displayed so you know that OpenOffice.org is recording)
• Perform an appropriate series of operations
• Click the Stop Recording button and save the macro, giving it a name
The macro names given here are just suggestions. You can choose any allowed (begin with a letter, don't use spaces) name when you record your macro.
Name Description Record these steps PasteFormats After a selection of cells has Edit → Paste Special → Selection:Formats been copied to the clipboard, → Options:None → Operations:None paste just their formatting/style → ShiftCells:Don't → OK PasteFormulas After a selection of cells has Edit → Paste Special been copied to the clipboard, → Selection:Formulas → Options:None paste just their formulas (no → Operations:None → ShiftCells:Don't formatting) → OK PasteValues After a selection of cells has Edit → Paste Special been copied to the clipboard, → Selection:Text,Numbers,Date&Time paste just their values (no → Options:None → Operations:None formulas/formatting) → ShiftCells:Don't → OK PasteUnformatted After text/web content has been Edit → Paste Special copied to the clipboard, paste it → Selection:UnformattedText → OK without formatting (text only) Duplicate Fill the content of the cells above «Shift+UpArrow» → Edit → Fill → Down the selected range down to it → «DownArrow» CutRows Move all the cells in the current «Shift+Space» → Edit → Cut → Edit row to the clipboard and delete → Delete Cells the row InsertCut After using CutRows, insert new «Shift+Space» → Edit → Paste → Edit rows above the current row and → Undo → Insert → Cells → Edit → Paste paste the clipboard there FillRightDown In a selection, fill the content Edit → Fill → Right → Edit → Fill → Down of the upper left cell to all the others; in a single column, fill the top selected cell down; in a single row, fill the left cell to the rightI use all of them often, so each one has a keyboard shortcut assigned.
The first three macros are used to copy just part of a cell or cell range: the format, the formula, or the value. When using Styles, PasteFormat copies the style from one cell to a range. PasteValues converts formulas to values. To record PasteUnformatted you must first place some text on the clipboard, so first copy something from a text document or a web page, not from Calc. This macro is useful for triggering the Text Import dialog. Duplicate copies the formula or value in the cells above; it is useful if you've inserted a new row and need to copy formulas from above to it. The macro can fill multiple rows, but active cell must be in the row immediately below the one you are copying from. CutRows and InsertCut are used to move rows up or down. The Mac user interface does not provide a simple way to do that. The «Shift+Space» keyboard shortcut selects the current row. The Paste and Undo steps select the correct number of rows for the Insert → Cells operation. FillRightDown is useful for populating a formula into a cell range.
LibreOffice may provide other ways to perform some of these actions. I do not use LibreOffice. I realize that some of these actions may be available in OpenOffice via the context menu. I prefer to use keyboard shortcuts.