How to copy columns to new sheet using macros LibreOffice

[sidas119]

Hello i wanna copy just some specifics columns not all sheet i cannot get any info of that i writted this code and this code copy all columns and paste into new sheet its okey i need new sheets but i need select just specifics columns

Sub CopySheet
    Dim oCurrentController As Object
    Dim oActiveSheet As Object
    Dim oSheets As Object
    oCurrentController = ThisComponent.getCurrentController()
    oActiveSheet = oCurrentController.getActiveSheet()
    oSheets = ThisComponent.getSheets()
    If oSheets.hasByName( oActiveSheet.Name & "Copy" ) Then
        MsgBox "Sheet name " & oActiveSheet.Name & "Copy, already exists"
    Else
       oSheets.copyByName(oActiveSheet.Name, oActiveSheet.Name & "Copy", oActiveSheet.RangeAddress.Sheet  + 1)
    End If
End Sub

Tagging fixed by ajlittoz

[ajlittoz]

Tagging is not consistent with your question text. base is for questions related to database inrerface component Base, while it looks like you’re asking for spreadsheets, which tag is calc. Please edit the tags to give your question more “visibility”.

[Pascalb]

Hi,

A solution is to get each cell:
Cell = oSheet.getCellByPosition(Column, Row)

Sub Copy_cells
Dim oDocument as Object, oSheets as Object, oSheet1 as Object, oSheet2 as Object

oDocument = thisComponent
oSheets = oDocument.Sheets
oSheet0 = oSheets.getByName("Sheet1") ' or oSheets.getByIndex(0)
oSheet1 = oSheets.getByName("Sheet2") ' or oSheets.getByIndex(1)
for i = 0 to 10
	for j = 0 to 100
		oSheet1.getCellRangeByPosition(i,j).value=_
		oSheet0.getCellRangeByPosition(i,j).value
	next
next

End Sub

This macro copy 100 lines on 10 columns.

You have other ways to copy by range, if you want to copy a whole column. My way is easy to modify or make a selection of cells.

Another way, I give it as is:

Sub UpdateThisWeek

Dim Doc As Object
Dim ThisWeek As Object
Dim Steering As Object
Dim Source As Object
Dim Target As Object
Dim Week as Integer

Doc = ThisComponent
ThisWeek = Doc.Sheets.getByName("This week")
Steering = Doc.Sheets.getByName("Steering")
Week = Steering.getCellByPosition(6,4).Value
Source = ThisWeek.getCellRangeByName("H12:H206")
Target = Steering.getCellRangeByName("M12:AU206").getCellRangeByPosition(Week-19,0,Week-19,194)

Dim i, s
For i = 0 To 194
    s = Source.getCellByPosition(0, i).Value
    If s > 0 Then
        Target.getCellByPosition(0, i).Value = s
    Else
        Target.getCellByPosition(0, i).String = ""
    End If
Next i

End Sub

[elmau]

A column is a cell range too. Of course, the end row of source range can be get with a cursor.

Sub copy_range()

	doc = ThisComponent
	sheet = doc.CurrentController.ActiveSheet
	
	source = sheet.getCellRangeByName("B1:C11")
	
	doc.Sheets.insertNewByName("New", 0)
	
	target = doc.Sheets(0).getCellRangeByName("A1")
	
	sheet.copyRange(target.CellAddress, source.RangeAddress)
	
End Sub

[sidas119]

Its work fine how to copy couple columns i wanna like A1 will be first as copied and B2 second and more more, i try this method its not working

Sub copy_range()

doc = ThisComponent
sheet = doc.CurrentController.ActiveSheet

source = sheet.getCellRangeByName("B1:B100", "C1:C100")


doc.Sheets.insertNewByName("New", 0)

target = doc.Sheets(0).getCellRangeByName("A1", "B2")


sheet.copyRange(target.CellAddress, source.RangeAddress)

End Sub

[sidas119]

ITS WORK THANKS !!!

Sub copy_range()

doc = ThisComponent
sheet = doc.CurrentController.ActiveSheet

source1 = sheet.getCellRangeByName("I5:I1000")
source2 = sheet.getCellRangeByName("B5:B1000")
source3 = sheet.getCellRangeByName("D5:D1000")
source4 = sheet.getCellRangeByName("C5:C1000")
source5 = sheet.getCellRangeByName("L5:L1000")
source6 = sheet.getCellRangeByName("Q5:Q1000")

doc.Sheets.insertNewByName("MtForma", 0)

target1 = doc.Sheets(0).getCellRangeByName("A1")
target2 = doc.Sheets(0).getCellRangeByName("B1")
target3 = doc.Sheets(0).getCellRangeByName("C1")
target4 = doc.Sheets(0).getCellRangeByName("D1")
target5 = doc.Sheets(0).getCellRangeByName("E1")
target6 = doc.Sheets(0).getCellRangeByName("F1")

sheet.copyRange(target1.CellAddress, source1.RangeAddress)
sheet.copyRange(target2.CellAddress, source2.RangeAddress)
sheet.copyRange(target3.CellAddress, source3.RangeAddress)
sheet.copyRange(target4.CellAddress, source4.RangeAddress)
sheet.copyRange(target5.CellAddress, source5.RangeAddress)
sheet.copyRange(target6.CellAddress, source6.RangeAddress)

End Sub

[karolus]

Without ugly copy&paste code!
And of course … its python:

def copy_ranges(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    doc.Sheets.insertNewByName("MtForma",0)
    source_sheet = doc.CurrentController.ActiveSheet
    target_sheet = doc.Sheets["MtForma"]
    for i, col_name in enumerate(("I","B","D","C","L","Q")):
        source = source_sheet[f'{col_name}5:{col_name}1000'].RangeAddress
        target = target_sheet[0,i].CellAddress
        source_sheet.copyRange(target, source)

[ajlittoz]

Are you sure you want a macro for that? A matrix formula would avoid all the fuss around macros and be faster. In addition, no need to rerun the macro when data is changed in the source area (formulas are by default auto-updated).

[elmau]

You can only pass one range at a time in the getCellRangeByName method.

https://wiki.documentfoundation.org/Macros/Basic/Calc/Ranges#Get_range_by_name