I am using Ubuntu 22.04 and LibreOffice 24.2.0.3. Suppose there are values in Column A and Column B and in some of the cells in Column C there is a formula. (Remember that in column C every cell do not contain a formula.) I used to copy formula from one cell in Column C and then paste it in another cell in Column C. If I copy something else then after that if I again want to copy that particular cells, I have to go to that cell, then I have to copy it
, then I have to go the cell where I want to paste the formula then I can paste it. Is there a way by which if I press any shortcut key or toolbar button so that I will get the formula in desired cell of Column C. Or something like Autotext or autocorrect feature which will solve my problem.
My workaround tips:
- Try to complete one task, before you begin an another one. Then you not need any helper tools.
- Write (or record) a macro what will COPY the formula from a specific cell, and what will PASTE the formula into the target cell selected by a mouse click.
.
The clipboard manager softwares can handle more than one clipboard content. Do not use them! The LibreOffice sometimes will conflict with them.
- My one task is related to that row. I must complete the row first.
- I tried to record the macro but when I run the macro it pastes to the same cell which I used at the time of recording macro, which is not expected. I want to paste the formula in current cell.
- I noticed libreoffice sometimes conflict with clipboard manager.
Record the macro in separated parts, and do not record the cell selection for the paste part. Then the PAste will happened in the actually selected cell.
You can write an another macro what will call the parts (MyCopy and MyPast) of the desired main macro.
Sub MyCopyPaste
MyCopy
REM there is not any cell selection here
MyPaste
end sub
Please tell me what changes should be done in the following formula
sub Formula
rem ----------------------------------------------------------------------
rem define variables
dim document as object
dim dispatcher as object
rem ----------------------------------------------------------------------
rem get access to the document
document = ThisComponent.CurrentController.Frame
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
rem ----------------------------------------------------------------------
dim args1(0) as new com.sun.star.beans.PropertyValue
args1(0).Name = "ToPoint"
args1(0).Value = "$C$1"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args1())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
rem ----------------------------------------------------------------------
dim args3(0) as new com.sun.star.beans.PropertyValue
args3(0).Name = "ToPoint"
args3(0).Value = "$C$3"
dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args3())
rem ----------------------------------------------------------------------
dispatcher.executeDispatch(document, ".uno:Paste", "", 0, Array())
end sub
My sample code written by based on the API and the Dispatcher.
Sub MyCopyPaste
dim oDispatcher, oDoc, oController, oSheet, oSourceCell, oTargetCell, oCellAddress as object
dim NoArg()
oDispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
oDoc = ThisComponent
oController = oDoc.getCurrentController
oSheet = oController.activesheet
oCellAddress = oController.Selection.getCellAddress()
oTargetCell = oSheet.getCellByPosition(oCellAddress.Column, oCellAddress.row)
oSourceCell = oSheet.getCellRangeByName("C2")
oController.Select(oSourceCell)
oDispatcher.executeDispatch(oController, ".uno:Copy", "", 0, NoArg())
oController.Select(oTargetCell)
oDispatcher.executeDispatch(oController, ".uno:Paste", "", 0, NoArg())
End Sub
This code does not contain any protection. You can overwrite any cell accidentally by usage of this code. You can create conditions: “The code will work only in the column C (column Nr 2)”. The colunms and rows are numbered with 0 starting value IN THE MACRO CODES.
No. Some clipboard managers conflict with LibreOffice because they ignore or break clipboard details.
Create a Named Expression (Ctrl+F3 or Sheet → Named Ranges and Expressions → Define), add the name and enter a formula expression instead of a cell range reference. Note that relative references in that expression are relative to the cell on which the named expression is defined and are automatically adjusted if the name is used in another cell. Then use the name as cell formula, e.g. =MyName
I tested with “a”, and =a works. You can’t use “r” or “c” (row, column).