LibreOffice Calc macro to copy a range from one sheet to a different location in a different sheet

Question

Using a macro, I want to copy whatever contents are in Sheet1,B2:D5 into Sheet2,A1 as a 'header cell'. There are numerous 'flirting around the edge' pages in the web on this but I can't uncover anything specific and I'm not able to adapt their code what I want to do, which is hugely disappointing as I had hoped to be be able to 'self generate' code in LO basic by now but apparently not :( Would someone be so kind as to post a simple macro as to how to do this?

Edit: Here's where I am...

Sub try6

    Dim oSourceSheet as Object
    Dim oTargetSheet as Object
    Dim SourceRange As New com.sun.star.table.CellRangeAddress ' SourceRange with later Sheet1 like properties of what to copy '
    Dim TargetRange As New com.sun.star.table.CellAddress  ' TargetRange with later Sheet2 like properties of what to copy '

    oSourceSheet = ThisComponent.Sheets.getByName("Sheet1")
    oTargetSheet = ThisComponent.Sheets.getByName("Sheet2")   
    oSourceRange = oSourceSheet.getCellRangeByName("B2:D3") ' Specify the range to copy
    oTargetRange = oTargetSheet.getCellRangeByName("A1") ' Specify the header cell for pasting

    oTargetSheet.copyRange(TargetRange, SourceRange) ' Now nothing at all happens when the macro triggering button is pressed
    
End Sub

P.S. I can do it using loops, but as a 'paste block range' is where I fall down but that's how I want to do it.

Answer 1

    Sub Copy_a_Range
    'The following example copies the B2:C3 range on Sheet1 to the range starts at position A6 on Sheet2
    'Reference:  https://wiki.documentfoundation.org/Documentation/BASIC_Guide#Inserting_and_Deleting_Rows_and_Columns  
    Dim Doc As Object
    Dim Sheet As Object
    Dim Target_Sheet As Object
    Dim CellRangeAddress As New com.sun.star.table.CellRangeAddress
    Dim CellAddress As New com.sun.star.table.CellAddress
    
    Doc = ThisComponent
    Sheet = Doc.Sheets(0)
    Target_Sheet = Doc.Sheets(1)
    
    ' SOURCE:
    CellRangeAddress.Sheet = 0
    CellRangeAddress.StartColumn = 1
    CellRangeAddress.StartRow = 1
    CellRangeAddress.EndColumn = 2
    CellRangeAddress.EndRow = 2
    
    ' TARGET:
    CellAddress.Sheet = 1
    CellAddress.Column = 0
    CellAddress.Row = 5
    
    Target_Sheet.copyRange(CellAddress, CellRangeAddress)
    End Sub


' ------------------
' OR...
'I've since come across this useful page in
' dealing with ranges:
' wiki.documentfoundation.org/Macros/Basic/Calc/Ranges –

Sub New_Copy_Range
sheet = ThisComponent.CurrentController.ActiveSheet
    source = sheet.getCellRangeByName("B1:F3")
    target = sheet.getCellRangeByName("c16")
    sheet.copyRange(target.CellAddress, source.RangeAddress)
End sub

Answer 2

You can record a macro in LibreOffice, but it's limited

using this function, LibreOffice created, with some smal changes in the format:

REM  *****  BASIC  *****

sub try1
    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 args2(0) as new com.sun.star.beans.PropertyValue
    args2(0).Name = "ToPoint"
    args2(0).Value = "$B$2:$D$5"

    dispatcher.executeDispatch(document, ".uno:GoToCell", "", 0, args2())

    rem ----------------------------------------------------------------------
    dispatcher.executeDispatch(document, ".uno:Copy", "", 0, Array())

    rem ----------------------------------------------------------------------
    dim args4(0) as new com.sun.star.beans.PropertyValue
    args4(0).Name = "Nr"
    args4(0).Value = 2

    dispatcher.executeDispatch(document, ".uno:JumpToTable", "", 0, args4())

    rem ----------------------------------------------------------------------
    dim args5(5) as new com.sun.star.beans.PropertyValue
    args5(0).Name = "Flags"
    args5(0).Value = "SVD"
    args5(1).Name = "FormulaCommand"
    args5(1).Value = 0
    args5(2).Name = "SkipEmptyCells"
    args5(2).Value = false
    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())

end sub

EDIT: Recordsing a Mavro in Excel2013 produces:

Sub Macro1()
'
' Macro1 Macro
'

    Range("B2:D5").Select
    Selection.Copy
    Sheets("Sheet2").Select
    Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, SkipBlanks _
        :=False, Transpose:=False
End Sub

But this will not work in LibreOffice Calc