Macros/ScriptForge/CopyRangeofCells
Appearance
< Macros | ScriptForge
TDF LibreOffice Document Liberation Project Community Blogs Weblate Nextcloud Redmine Ask LibreOffice Donate
How to copy a range of cells
Authored by Jean-Pierre Ledure.
The example shows how to copy a range of cells to a defined destination. Four alternatives are presented:
- Copy the range to another cell located in the same file
- Copy the range to another cell located in another spreadsheet
- Copy the range to another range located in the same file
- Store the range as a PDF file
The initial range is populated arbitrarily with scalars and formulas. The targeted range is specified with an interactive range selector.

How to run it in BASIC
- Create a new document.
- Open the Basic IDE
- Select the first available blank module
- Copy and paste the Basic code
- Verify line 14
- Run Main()
How to run it in Python
- Create a new document
- Run APSO
- Create a new module, call it 'Module1'
- Copy and paste the Python code below
- Verify line 15
- Optionally, enter your parameters in lines 19+
- Run main()
Code
REM SCRIPTFORGE WIKI EXAMPLE
REM How to copy a cell range ?
REM Minimal required version: LibreOffice 7.6
REM Used services
REM Calc, FileSystem, Array
Public calc1 As Object
Public calc2 As Object
Public ui As Object ' The UI service
'*******************************************************************
'*** Define the input range
'*******************************************************************
Const inputrange = "$B$2:$L$9"
'*******************************************************************
'* Run the demo below
'*******************************************************************
Sub Main(Optional event As Object)
Dim copytype As String ' 1 = same file, 2 = other file
GlobalScope.BasicLibraries.loadLibrary("ScriptForge")
copytype = InputBox( "1 = Copy to cell in same file" & Chr(10) _
& "2 = Copy to cell in other file" & Chr(10) _
& "3 = Copy to range in same file (destination must be >= source)" & Chr(10) _
& "4 = Export range to a PDF file" _
, "Copy range")
If copytype = "" Or copytype = "0" Then Exit Sub
DefineInputRange()
CopyInputRange(copytype)
End Sub
'*******************************************************************
'* Specific demo code
'*******************************************************************
Sub CopyInputRange(copytype As String)
Dim target As String
Dim inputarea As Object
Dim fs As Object
Select Case copytype
Case "1" ' Copy to same file, same or other sheet
target = calc1.OpenRangeSelector("Select the target cell", singlecell := True)
If target = "" Then Exit Sub
calc1.CopyToCell(inputrange, target)
Case "2" ' Copy to another file
Set calc2 = ui.CreateDocument("Calc")
target = calc2.OpenRangeSelector("Select the target cell", singlecell := True)
inputarea = calc1.Range(inputrange) ' Make a reference from the range given as a string
calc2.CopyToCell(inputarea, target)
Case "3" ' Copy to range in same file
target = calc1.OpenRangeSelector("Select the target range of cells", singlecell := False)
If target = "" Then Exit Sub
calc1.CopyToRange(inputrange, target)
Case "4"
fs = CreateScriptService("FileSystem")
target = fs.PickFile(mode := "SAVE")
calc1.ExportRangeToFile(inputrange, target, imagetype := "PDF", overwrite := True)
Case Else
End Select
End Sub
'*******************************************************************
'* Compute the content of the input range
'*******************************************************************
Sub DefineInputRange()
' The range contains scalars in the 1st row and formulas elsewhere
Dim toprow As String ' The 1st row of the input range
Dim firstcell As String ' Top-left cell of the input range
Dim formula As String ' The formula to apply across the range
Set ui = CreateScriptService("UI")
Set arr = CreateScriptService("Array")
Set calc1 = ui.CreateDocument("Calc")
toprow = calc1.Offset(inputrange, height := 1)
calc1.SetValue(toprow, arr.RangeInit(1, calc1.Width(toprow)))
firstcell = Replace(calc1.FirstCell(inputrange), "$", "")
formula = "=" & firstcell & "+1"
calc1.SetFormula(calc1.Offset(inputrange, 1, height := calc1.Height(inputrange) - 1), formula)
End Sub
# coding: utf-8
from __future__ import unicode_literals
from scriptforge import CreateScriptService
### SCRIPTFORGE WIKI EXAMPLE
### How to copy a cell range ?
### Minimal required version: LibreOffice 7.6
### Used services
### Calc, FileSystem, Basic
basic = CreateScriptService('Basic')
# *******************************************************************
# *** Define the input range
# *******************************************************************
inputrange = '$B$2:$L$9'
# *******************************************************************
# * Run the demo below
# *******************************************************************
def main():
copytype = basic.InputBox('1 = Copy to cell in same file\n'
+ '2 = Copy to cell in other file\n'
+ '3 = Copy to range in same file (destination must be >= source)\n'
+ '4 = Export range to a PDF file'
, 'Copy range')
if copytype == '' or copytype == '0':
return None
calc1 = defineinputrange()
copyinputrange(calc1, copytype)
# *******************************************************************
# * Specific demo code
# *******************************************************************
def copyinputrange(calc1, copytype):
ui = CreateScriptService('UI')
if copytype == '1': # Copy to same file, same or other sheet
target = calc1.OpenRangeSelector('Select the target cell', singlecell = True)
if target == '':
return None
calc1.CopyToCell(inputrange, target)
elif copytype == '2': # Copy to another file
calc2 = ui.CreateDocument('Calc')
target = calc2.OpenRangeSelector('Select the target cell', singlecell = True)
inputarea = calc1.Range(inputrange) # Make a reference from the range given as a string
calc2.CopyToCell(inputarea, target)
elif copytype == '3': # Copy to range in same file
target = calc1.OpenRangeSelector('Select the target range of cells', singlecell = False)
if target == '':
return None
calc1.CopyToRange(inputrange, target)
elif copytype == '4':
fs = CreateScriptService('FileSystem')
target = fs.PickFile(mode = 'SAVE')
calc1.ExportRangeToFile(inputrange, target, imagetype = 'PDF', overwrite = True)
return None
# *******************************************************************
# * Compute the content of the input range
# *******************************************************************
def defineinputrange():
# The range contains scalars in the 1st row and formulas elsewhere
# toprow The 1st row of the input range
# firstcell Top-left cell of the input range
# formula The formula to apply across the range
ui = CreateScriptService('UI')
calc1 = ui.CreateDocument('Calc')
toprow = calc1.Offset(inputrange, height = 1)
calc1.SetValue(toprow, tuple(range(1, calc1.Width(toprow) + 1)))
firstcell = calc1.FirstCell(inputrange).replace('$', '')
formula = '=' + firstcell + '+1'
calc1.SetFormula(calc1.Offset(inputrange, 1, height = calc1.Height(inputrange) - 1), formula)
return calc1
g_exportedScripts = (main,)
if __name__ == "__main__":
main()