Home      Contact    Case studies    Computer help    Sitemap
Isle of Wight computer help


Libreoffice Macro Help

A basic macro to get the active cell and copy and paste its
contents in a Libreoffice / Openoffice.org Calc spreadsheet

I needed a simple macro to retrieve the value in the currently selected cell and copy it to a fixed cell in another sheet within the same spreadsheet (workbook). The macro recorder didn't seem to be able to do it (and the code it generates is not very easy on the eye). I searched the web for a ready made solution with no luck.

Here is my effort, which does the job. Hope it helps...

REM name subroutine:
sub copy_to_other_sheet

REM Declare variables:
Dim Doc
Dim destinationsheet
Dim sourcecell
Dim cell
Doc = ThisComponent

REM Get active cell in active sheet and store in sourcecell variable:
sourceCell = Doc.CurrentController.Selection

REM My destination sheet is called Invoice - change to suit:
destinationsheet = Doc.Sheets.getbyname("Invoice")

REM Bring destination sheet to the front:
Doc.GetCurrentController().setActiveSheet(destinationsheet)

REM Copy to the destination:
Cell = destinationSheet.getcellRangeByName("K14")
Cell.Value = sourcecell.DataArray(0)(0)
end sub

DataArray(0) (0) gives the value in top left (0,0) of a range if one has been selected.

Could have just used sourcecell.Value instead, but you'd get an error if more than one cell were selected when you run the macro...
There's no error checking, if the source cell contains text rather than a number, you'll get a zero or some other unexpected result.

Tested in LibreofficeVersions:
4.1.0.4, Build ID: 410m0(Build:4) on Ubuntu Linux 12.04.2 LTS.
4.1.3.2m Build ID: 410m0(Build:2) on Mint Linux 16
May or may not work on anything else...

If you're on the Isle of Wight (UK) and need computer repair or help with Office, macro programming etc, give me a call!



IOW PC - Isle of Wight ( UK ) computer help for you & your business
0789 150 9466 / 01983 856093