It seems to me this is a very recurring question for which I have been completely unable to find any documentation whatsoever, let alone a good answer.
I came across a solution purely by trial and error using LibreOffice 4.0, but the solution also seem to work for older versions of OpenOffice and LibreOffice.
After having read a lot of comments with a lot of partial suggestions (which I'm sad to say I have found rather hopeless to check out and get to work), I will instead provide all the details needed to replicate the solution.
Create a LibreOffice Calc document called MacroTest.odf.
Inside the document, rename the worksheet named Sheet1 to TestSheet (the tab on the bottom left).
Create a macro by going to Tools / Macros / Organize Macros / LibreOffice Basic.
Important! This macro must be linked to the file, so you can NOT use My Macros.
Under MacroTest / Standard, click on New (button on the right), and accept the name Module1.
Paste the following (read: at the end of this text) code into the editor and save.
Switch back to the worksheet, enter 123 into A1 and then
make A1 the active cell by clicking on it.
Switch back to the macro, place the cursor at the top. Now press F5 and click on Run.
Switch back to the worksheet and... see any difference?
If this works / doesn't work for you, or if you find this at all useful, I'm happy to read about it...
Regards,
Vegard Bakke
Norway
--------------------------------
' How to get data from the active cell (where the cursor is) and put it somewhere else inside a given worksheet.
Code: Select all
Option VBASupport 1
Sub CopyFromActiveCell()
; Initialize the variables (not all that neccessary, but it makes for tidy programming)
Dim activecellvalue
Dim activerow as Long
Dim activecolumn as Long
' Specify worksheet with active/current cell
With Worksheets("TestSheet")
' Assign the active cell's value, row and column to local variables
' Do a test by entering a value in any cell, then make it the active cell, run the macro and watch the magic :-)
activecellvalue = ActiveCell.Value
activerow = ActiveCell.Row
activecolumn = ActiveCell.Column
' Copy the active cell's value to a cell below and to the right of the active cell
Cells(activerow+1,activecolumn+1) = activecellvalue
' Copy the active cell's value to range B5 x D6
.Range(Cells(5,2), Cells(6,4)).Value = activecellvalue
End With
End Sub