Adding new row to bottom of current worksheet

[celcattech]

Hi,

We're trying to create a Macro to add a new row to an exisitng worksheet after the bottom row that is populated. We also need to copy the conditional formatting from the row above it. We can do this using the mouse drag but would like to achieve this via a Macro if possible

Anyone know how to do this?

Thanks in advance

Chris

[JohnSUN-Pensioner]

Welcome to forum, Chris!
Of course it's possible:

Code: Select all

REM Get active sheet and run macro duplicateFormatsAndFormulas
Sub dublRow()
Dim oCurrentController As Object
Dim oActiveSheet As Object
	oCurrentController = ThisComponent.getCurrentController()
	oActiveSheet = oCurrentController.getActiveSheet()
	Call duplicateFormatsAndFormulas(oActiveSheet)
End Sub

REM Copy last row without values
Sub duplicateFormatsAndFormulas(oSheet As Object)
Dim oRows As Object
Dim nRow As Long
Dim oRowSourse As Object
Dim oRowTarget As Object
Dim aCell As Object
	GlobalScope.BasicLibraries.LoadLibrary("Tools")
	nRow = GetLastUsedRow(oSheet)

	oRows = oSheet.getRows()
	oRowSourse = oRows.getByIndex(nRow)
	oRowTarget = oRows.getByIndex(nRow+1)
	aCell = oRowTarget.getCellByPosition(0, 0).getCellAddress()
	oSheet.copyRange(aCell, oRowSourse.getRangeAddress())
	oRowTarget.clearContents(7) '  Remove text, numbers and dates, leave formats&formulas
REM See detail http://api.openoffice.org/docs/common/ref/com/sun/star/sheet/CellFlags.html
End Sub