HowTo CopyToCell without confirmation dialog

I have a basic macro in LO Calc 7.3 to insert range and copy cells into, and when I fire this macro CopyToCell triggers dialog asking if I want to replace values in cells (A6:D6)

Sub AddNewDate
	Set dDoc = CreateScriptService("Document", "MyTable.ods")
	cTdy = dDoc.GetValue("sh1.A5")
	dDoc.CopyToCell("sh1.A7:D7", "sh1.A6")

I didn’t see any argument in the reference manual for CopyToCell, that could suppress this dialog.

See under Tools->Options->LibreOfficeCalc->General,
if it is marked “show overwrite warning when pasting data”, uncheck it.

1 Like

My test shows no problem with the code (provided a correction is made EndSub s/b End Sub).
There is also a line:

cTdy = dDoc.GetValue("sh1.A5")

that does nothing in that code.
Now if you look at the logic of the code:


inserts a blank area (probably). With that being the case, the dialog should not appear. It seems the code you are using may be different than what is posted here.
I mention probably here because it is known that new columns ( and I seem to recall new rows, including partials?) are not really new at all but rather a copy and wipe. Problem is not all data gets wiped. This is probably very remote in this case. See tdf#109412

1 Like

Thanks for info, I haven’t noticed your reply earlier. You are right I had more code, but mostly just copying cells, but I missed the line that is giving me confirmation message and that is
dDoc.CopyToRange("sh1.A8", "sh1.A6:A7")
Also variable 'cTdy 'is used later in the code, for this example is not needed.
I need to populate formulas, as they get repeated or skip a line when doing insert

Thanks @Gbp, that could be a solution, although I like this dialog when doing copy/replace manually so I double check every time. I thought there is something I could do within the code.

Depending on what you need copied (do you need formats, etc.) have you also considered just assigning using getDataArray and setDataArray straight from UNO?

No I don’t have an experience with UNO, are you kind enough to post an example to copy range of cells to another range.
I do want to copy everything, formulas and formats, and values as a result. Thanks

A range of solutions can be seen at Macro to Copy Cell values, formats, and image - #11 by mauricio.

Here is a solution based on one by @mauricio that seems to “just work” and makes perfectly good sense:

Option Explicit

Sub Test()
	CopyAll "Sheet1","C9","Sheet2","J6"
End Sub

Sub CopyAll(SourceSheetName As String, SourceCellName As String, TargetSheetName As String, TargetCellName As String)
	Rem Based on by mauricio
	Dim Doc As Object
	Dim Source As Object
	Dim TargetSheet As Object
	Dim Target As Object
	Doc = ThisComponent
	Source = Doc.Sheets.getByName(SourceSheetName).getCellRangeByName(SourceCellName)
	TargetSheet = Doc.Sheets.getByName(TargetSheetName)
	Target = TargetSheet.getCellRangeByName(TargetCellName).getCellByPosition(0,0)
	TargetSheet.copyRange(Target.CellAddress, Source.RangeAddress)
End Sub

The trick is to feed copyRange what it wants, a source range address but a target cell address (since whatever the source range size is, it just plops down with its upper left corner at the target cell).

1 Like