macroslibreofficecalc

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


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.


Solution

  •     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