BASIC Calc : How to copy a cell range from a row and paste to multiple rows?

[lonk]

Option Explicit

Sub	CopyA1D1AndPasteToA6D6
	CopyFromRangeA1D1AndPasteToRangeA6D6ByLooping(5)
End Sub

Sub CopyFromRangeA1D1AndPasteToRangeA6D6ByLooping(pRows&)
	Dim oDoc 						As Object : oDoc 	= ThisComponent
	Dim oSheet						As Object : oSheet	= oDoc.getCurrentController.ActiveSheet
	Dim SourceRange, TargetCell     As Object
'   Dim TargetRange                 As Object
	Dim i							As Long
	SourceRange = oSheet.getCellRangeByPosition(0, 0, 3, 0)
		For i = 1 To pRows
			TargetCell 	= oSheet.getCellByPosition(0, 0 + i)
'			TargetRange = oSheet.getCellRangeByPosition(0, 0 + 1, 3, 0 + 1 + pRows - 1)
			oSheet.copyRange(TargetCell.CellAddress, SourceRange.RangeAddress)
		Next
End Sub

How can we avoid looping using i and use TargetRange for multiple rows instead?

[Lupp]

You can use the fillAuto() method with FillDirection = 0 (TO_BOTTOM) and nSoureCount = 1: LibreOffice: XCellSeries Interface Reference.
(In a case where your source range has more than one row, the automatisms coming with fillAuto() may not meet your needs. If your source contains “incrementable constants” the result may also not be what you want. Look for fillSeries() then - or get back to copyRange(). )

[Lupp]

If you worry about efficiency in cases of very large target ranges: You can try a strategy of double-the-size-per-step.
I didn’t test the idea really thoroughly, however.
The consept is exemplified here: Calc: Copy one Cell to each cell in a CellRange (View topic) • Apache OpenOffice Community Forum

[newbie-02]

the fools way: record a macro …

---

sub test21

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 args1(0) as new com.sun.star.beans.PropertyValue

args1(0).Name = “ToPoint”

args1(0).Value = “$A$1:$D$1”

dispatcher.executeDispatch(document, “.uno:GoToCell”, “”, 0, args1())

rem ----------------------------------------------------------------------

dispatcher.executeDispatch(document, “.uno:Copy”, “”, 0, Array())

rem ----------------------------------------------------------------------

dim args3(0) as new com.sun.star.beans.PropertyValue

args3(0).Name = “ToPoint”

args3(0).Value = “$A$2:$D$6”

dispatcher.executeDispatch(document, “.uno:GoToCell”, “”, 0, args3())

rem ----------------------------------------------------------------------

dispatcher.executeDispatch(document, “.uno:Paste”, “”, 0, Array())

end sub 'test21

---

i know! that the code is ‘cruel’ at least, i know! it’s not very flexible, and i know! that it doesn’t fulfill all your requiremens / wishes …

but it proves a way to copy into a range, and might be something you can build up upon,

and … quite sure … our pro’s will throw a bunch of better solutions

reg.

b.