Page 1 of 1

[Solved] Calc: move, copy paste entire column

PostPosted: Tue Jan 25, 2011 3:36 pm
by marco_lazzarini
How can i move one or more columns from one address to another?
(maybe by cutting & paste the columns) to achieve the same result of the following code for excel:

With osheet
.Columns(sFrom).Select ' sFrom can be "AA:AC"
.Application.Selection.Cut
.Columns(sTo).Select ' sTo can be "D:D"
.Application.Selection.Insert XlInsertShiftDirection.xlShiftToRight
End With

I just need to cut away one (or more) column from column, let's say "AA:AA"
and insert it in column "D:D" shifting right the already existing columns.
Thank everyone in advance for any help

Re: Calc: move, copy paste entire column

PostPosted: Fri Jan 28, 2011 6:10 am
by FJCC
Here is one example. Notice that you have to track how the columns shift after you insert the new ones.
Code: Select all   Expand viewCollapse view
oSheet = ThisComponent.Sheets.getByName("Sheet1")
'insert three new columns
InsertRange = oSheet.getCellrangeByName("D1:F65536")
InsertRange_RangeAddress = InsertRange.RangeAddress
oSheet.insertCells(InsertRange_RangeAddress, com.sun.star.sheet.CellInsertMode.COLUMNS)

oSourceRange = oSheet.getCellrangeByName("AD1:AF65536") 'The insert above moves the columns that were AA:AC to AD:AF
oSourceRangeAddress = oSourceRange.RangeAddress
oTargetCell = oSheet.getCellrangeByName("D1")
oTargetCell_CellAddress = oTargetCell.CellAddress
oSheet.moveRange(oTargetCell_CellAddress, oSourceRangeAddress)

Re: Calc: move, copy paste entire column

PostPosted: Fri Jan 28, 2011 1:22 pm
by marco_lazzarini
Thanks FJCC for your kyndly reply.
I found an alternative solution that has a little problem: you can only move one column per time.
Still i didn't find the way to move a range of contiguous columns.
Here it is:

Private Sub MoveColumn(ByRef sFrom As String, ByRef sTo As String, ByRef osheet As Object)
Dim oColumn As Object
Dim oColumnAddress As Object
Dim oTargetCol As Object
Dim oTargetColAddress As Object ' com.sun.star.Table.CellAddress
Dim iDsp As Integer

If osheet.Columns.getByName(sTo).getRangeAddress().StartColumn < osheet.Columns.getByName(sFrom).getRangeAddress().StartColumn Then
' If i move col from following to previuos address (i.e. swap right to left positions), following column (where source is) are renumbered. I need a displacement that keep in mind this
iDsp = 1
End If

' Determine column where to move (Target)
Set oTargetCol = osheet.Columns.getByName(sTo)
' Insert an empty col
osheet.getColumns.insertByIndex oTargetCol.getRangeAddress().StartColumn, 1
' get object "CellAddress" of target col
Set oTargetColAddress = osheet.getCellByPosition(oTargetCol.getRangeAddress().StartColumn - 1, 0).getCellAddress()

' Determine column to be moved (Source)
Set oColumn = osheet.Columns.getByIndex(osheet.Columns.getByName(sFrom).getRangeAddress().StartColumn + iDsp)
' get object "RangeAddress" source col
Set oColumnAddress = oColumn.getRangeAddress()

' move the col
osheet.moveRange oTargetColAddress, oColumnAddress
' delete the moved (now empty) col
osheet.getColumns.removeByIndex osheet.Columns.getByName(sFrom).getRangeAddress().StartColumn + iDsp, 1
End Sub

Re: [Solved] Calc: move, copy paste entire column

PostPosted: Sat Oct 12, 2013 8:56 pm
by smartmarti
Did this work?
still confused how to make a macro for moving a column by copying and paste.. :(

Re: [Solved] Calc: move, copy paste entire column

PostPosted: Sat Oct 12, 2013 9:58 pm
by smartmarti
I need to do something quite simple. I need to move the columns in the top 3 rows as the one in the 3 row below.
Any suggestion?

Re: [Solved] Calc: move, copy paste entire column

PostPosted: Sun Oct 13, 2013 5:31 am
by FJCC
I recorded a macro that does what I think you want. Referring to the attached file, that data are initially arranged like the example on Sheet1 and you want them to end up looking like the data on Sheet2. Run the macro ShuffleCols while Sheet1 is the active sheet and it will change to have the data arranged like Sheet2.

Re: [Solved] Calc: move, copy paste entire column

PostPosted: Sun Oct 13, 2013 2:45 pm
by smartmarti
IT WORKS!!!!!
thank you so much!!!!!! You are a genius...
:D I am so grateful... you saved my day

Re: [Solved] Calc: move, copy paste entire column

PostPosted: Sun Oct 13, 2013 3:17 pm
by smartmarti
Your macro works wonderfully.

But I'd like to integrate that with another one I already have.
In the attached doc my starting point is Sheet 1 (lots of columns) so I wrote a macro to remove some of them (if you run Remove columns macro you'll get exactly what's on Sheet 2 now - the one we were starting on the previous post). And then if at that point you run your macro it doesn't work anymore.. it doesn't give me the expected result which should be the one in Sheet 3.
I have no idea how to fix this. I never did any programming in my life... it might be just a stupid correction to integrate both macros.. but I can't do by myself :crazy:

thanks agin for all your help!

Re: [Solved] Calc: move, copy paste entire column

PostPosted: Sun Oct 13, 2013 4:23 pm
by FJCC
In your initial example the initial pattern was positioned starting in column E. After your RemoveCols macro that pattern starts in column A. I have recorded a new macro called ShuffleCols_2 that works after RemoveCols has run.

Re: [Solved] Calc: move, copy paste entire column

PostPosted: Mon Oct 14, 2013 12:21 am
by smartmarti
Amazing!!!!

I honestly don't know how to say thank you! This is going to be so useful!

thanks a lot again