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