The following macro split column A in Sheet1 into 5 columns.
First select a cell in new sheet ie. Sheet2 and then run macro.
REM ***** BASIC *****
Sub Main
SplitColumn ( "Sheet1","A",5)
End Sub
Sub SplitColumn ( SheetName As String , ColumnName As String , ColumnsNo As Integer )
iSheetIndex = ThisComponent.CurrentSelection.CellAddress.Sheet
oSheet = ThisComponent.Sheets.getByIndex( iSheetIndex )
RowsNo = GetLastUsedRow( ThisComponent.Sheets.getByName ( SheetName ) )
' status bar progress indikator
oBar = THISCOMPONENT.CurrentController.StatusIndicator
iNum = 1
oBar. start ( "Split column " & ColumnName & " in sheet " & SheetName, RowsNo/ColumnsNo )
For nR = 0 to RowsNo/ColumnsNo
oBar.setValue ( nR )
For nC = 0 To ColumnsNo - 1
oSheet.getCellByPosition (nC,nR).setFormula("=" & SheetName & "." & ColumnName & iNum )
iNum = iNum + 1
Next nC
Next nR
oBar.End
End Sub
' from Tools library
Function GetLastUsedRow(oSheet as Object) as Integer
Dim oCell As Object
Dim oCursor As Object
Dim aAddress As Variant
oCell = oSheet.GetCellbyPosition(0, 0)
oCursor = oSheet.createCursorByRange(oCell)
oCursor.GotoEndOfUsedArea(True)
aAddress = oCursor.RangeAddress
GetLastUsedRow = aAddress.EndRow
End Function