Macro works in OpenOffice Calc but not LibreOffice Calc

SWHurt · March 4, 2026, 3:11pm

Here’s a simple subroutine to copy cells & paste them into other cells.

In the subroutine, obook refers to the collection of sheets in the workbook. The arguments sent to the subroutine are:
copySheet - the name of the sheet the cell values are copied from
copyCells - the cells to be copied from copySheet
pasteSheet - the name of the sheet the cell values are pasted to
pasteCell - the name of the cell on pasteSheet to begin pasting the values

For example, Call Copy_Paste_Special (sheet1, "$A$1:$A$12, sheet2, “$B$3”)
would copy cell values from A1-A12 on sheet 1 to B3-B14 on sheet 2.

I wrote the subroutine to be able to use PasteSpecial features, particularly the SkipEmptyCells. It runs fine in Open Office but not in Libreoffice.

Anyone know why?

Sub Copy_Paste_Special (copySheet,copyCells,pasteSheet,pasteCell) ' Use Paste Special to Move/Copy
rem Get copyCells from copySheet, Move to pasteCell on pasteSheet & clear copyCells values
dispatcher = createUnoService("com.sun.star.frame.DispatchHelper")
document=ThisComponent.CurrentController.Frame
  oBook.SetActiveSheet(copySheet)
  dim args2(0) as new com.sun.star.beans.PropertyValue
  args2(0).Name = "ToPoint" : args2(0).Value = copyCells
  dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())
  dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())
  oBook.SetActiveSheet(pasteSheet)
  dim args4(0) as new com.sun.star.beans.PropertyValue
  args4(0).Name = "ToPoint" : args4(0).Value = pasteCell
  dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args4())
  dim args5(5) as new com.sun.star.beans.PropertyValue
  args5(0).Name = "Flags" : args5(0).Value = "V"
  args5(1).Name = "FormulaCommand" : args5(1).Value = 0
  args5(2).Name = "SkipEmptyCells" : args5(2).Value = true
  args5(3).Name = "Transpose" : args5(3).Value = false
  args5(4).Name = "AsLink" : args5(4).Value = false
  args5(5).Name = "MoveMode" : args5(5).Value = 4
  dispatcher.executeDispatch(document, ".uno:InsertContents", "", 0, args5())
  oBook.SetActiveSheet(copySheet)
End Sub

Wanderer · March 4, 2026, 9:22pm

I guess you have a document to test this?
Could you upload it together with your macro?
.
As you didn’t use code-tags (symbol </> when editing), the source is a bit messed up by this site (compare Can typographic quotes be switched off for ask.libreoffice.org?. )

Regina · March 4, 2026, 11:55pm

When you record Paste Special in LibreOffice, the args have a different order than in your macro or in a recorded macro in AOO. Perhaps that matters?
LibreOffice has order: Flags, SkipEmptyCells, FormulaCommand, Transpose, MoveMode, AsLink
AOO has order: Flags, FormulaCommand, SkipEmptyCells, Transpose, AsLink, MoveMode

SWHurt · March 5, 2026, 12:54am

I’ll test if the order makes a difference

Bal

sokol92 · March 5, 2026, 12:52pm

Your macro works fine for me in LO 25.8.4.
Open the attached file and run the Test macro.
I added two lines to your text:

  Dim oBook, dispatcher, document ' 2026-03-05
  oBook = ThisComponent.CurrentController ' 2026-03-05

PasteSpecial.ods (8.3 KB)

SWHurt · March 5, 2026, 5:34pm

Once I’d written an ods to give to Watcher, I was able to make it work much as you had. However, in its original context it still does not work. The copyCells are not pasted into the pasteCell location. To Regina’s point, it makes no difference how the arguments are ordered for the InsertContents.

I’m left with trying to run down the difficulty in my own sheets as clearly the subroutine can be made to function.

Thanks to all for your attention to my question.