Create keyboard shortcut to insert a row and copy the line above

[pdeman]

Hi,

I am trying to do a keyboard shortcut that insert a row in libreoffice calc (I have don’t that using tools->customize->keyboard)
but I’ld like that the shortcut copy as well the line above (or below, I can deal with both).
I don’t want to insert a blank line, but to insert a copy of the line above (and if it’s possible that it’s copying only the two first column of the line above and not the whole line, but I don’t think it’s possible).

I tried to do a macro, but it always copy the cells that I copied during the recording.
(args2(0).Value = “$A$4-$B$4”.
it doesn’t adapt the where I click.

Regards,
Pierre

[Lupp]

Recorded macros are never “sensitive” in the sense you expected. Read the code and you see the reason.

Any “awarenes” of a macro for selections or the content of controls requires explicit coding.

[Lupp]

Surely there isn’t a command for this compound task that you simply can assign to a keyboard shortcut. You first have to create a routine for the purpose. Doing this you need to explicitly specify where the rows shall be inserted.

I made a demo inserting as many rows as currently are selected above the selected range and filling the new rows with the content of as many rows from above the inserted ones.
Considering the specifics it might be preferable to insert below and to take the contents from the rows selected in advance.
Simply adapt the routine as needed.

Please note: If you want to use the Sub, you should copy it from the example document to a module of your ‘Standard’ library of BASIC macros.

(Edit:) To ease the comparison I add the code of the Sub contained in the above mentioned demo. You will see that the Sub is clearly “row-oriented” and uses the current selection only to define the range of rows for insertion and for the source (complete rows again) for the subsequent filling.

Sub doInsertAndFillRows
REM Thanks to the teachers, in specific to Andrew Pitonyak
REM This Sub was tested under LibO V 5.3.3.
REM It was assigned to Ctrl+Alt+P for the tests.
    Dim theDoc   As Object,  theFrame As Object, theSel     As Object
    Dim theSheet  As Object, theDispH As Object, interimSel As Object
    Dim sR As Long, eR As Long

theDoc    = ThisComponent
theFrame  = theDoc.CurrentController.Frame
If NOT theDoc.SupportsService("com.sun.star.sheet.SpreadsheetDocument") Then
    Exit Sub
End If

theSel    = theDoc.CurrentSelection
If NOT theSel.SupportsService("com.sun.star.sheet.SheetCellRange") Then
    Exit Sub
End If

theSheet  = theDoc.Sheets(theSel.RangeAddress.Sheet)
sR        = theSel.RangeAddress.StartRow
eR        = theSel.RangeAddress.EndRow 
If (sR*2 - eR - 1) < 0 Then
    Exit Sub
End If

theDispH  = CreateUnoService("com.sun.star.frame.DispatchHelper")
theDispH.ExecuteDispatch(theFrame, ".uno:InsertRowsBefore", "", 0, Array())

interimSel= theSheet.GetCellRangeByPosition(0, sR*2 - eR - 1, 1023, sR - 1) '(left, top, right, bottom)
theDoc.CurrentController.Select(interimSel)

theDispH.ExecuteDispatch(theFrame, ".uno:Copy", "", 0, Array())

interimSel = theSheet.GetCellRangeByPosition(0, sR, 1023, eR)
theDoc.CurrentController.Select(interimSel)

theDispH.ExecuteDispatch(theFrame, ".uno:Paste", "", 0, Array())

End Sub

[librebel]

Hello @pdeman, perhaps you could use the following macro for your purpose:

Sub CopyRangeInsertAfter()
REM Copies the cells of the currently selected Range into a new Range inserted below the selection.
	Dim oSheet, oSelection, oSource, oTarget
	oSheet = ThisComponent.CurrentController.ActiveSheet
	oSelection = ThisComponent.getCurrentSelection()
	oSource = oSelection.getRangeAddress()
	oSheet.Rows.insertByIndex( oSource.EndRow + 1, 1 + oSource.EndRow - oSource.StartRow )
	oTarget = oSheet.getCellByPosition( oSource.StartColumn, oSource.EndRow + 1 ).CellAddress
	oSheet.copyRange( oTarget, oSource )
End Sub