Incremental spreadsheet filling with macros

Imagine your spreadsheet contains some data on rows 1,2,3.
You want to create a macro that inserts data on row 4. Next time, it will insert on row 5. Next time, it will fill in row 6, etc.
How would you do that ? (the increment)

Yes, it is possible, post an example file from your Database spreadsheet, edit your question and with the clip icon add.

It can be something like this:

Sub InsertNextRow(sSheetName As String, aData As Variant)
Dim oSheets As Variant
Dim oSheet As Variant
	oSheets = ThisComponent.getSheets()
	If oSheets.hasByName(sSheetName) Then
		oSheet = oSheets.getByName(sSheetName)
		Globalscope.BasicLibraries.LoadLibrary("Tools")
		NewRow = GetLastUsedRow(oSheet) + 1
		oSheet.getCellRangeByPosition(0, NewRow, UBound(aData), NewRow).setDataArray(Array(aData))
	EndIf 
End Sub

Test it with

Sub testInsNewData
	InsertNextRow("Sheet1", Array(1,"Two","""text""",6))
	InsertNextRow("Sheet1", Array(2,3,"555"))
End Sub
1 Like

Hello @ychaouche, here is a simple example file.

image description


ATTENTION: If you would like to give more details to your question, use edit in question or add a comment below. Thank you.

If the answer met your need, please click on the ball Descrição da imagem to the left of the answer, to finish the question.