Page 1 of 1

Copy/Transpose

PostPosted: Wed Aug 16, 2017 8:05 pm
by det
I know how to copy and paste special (transpose,without format) a range by "hand".
How can I do this by a macro? I could record the macro, but I was told not to and rather write my own code.
I got as far as to copy the range to another sheet and cell.
But how do I transpose the range without Formulas formats?
Det
Here is my Code:

Code: Select all   Expand viewCollapse view
'     xxxxxxxx Copy a Range to same Sheet and to another Sheet  xxxxx

Dim oSheet
Dim oRangeAddress 'The range to copy
Dim oCellAddress 'Destination address
oSheet = ThisComponent.Sheets(2)
oRangeAddress = oSheet.getCellRangeByName("A1:B5").getRangeAddress()
oCellAddress = oSheet.getCellByPosition(2, 7).getCellAddress()
oSheet.copyRange(oCellAddress, oRangeAddress)
oSheet = ThisComponent.Sheets(3)
oCellAddress = oSheet.getCellByPosition(0, 9).getCellAddress()
oSheet.copyRange(oCellAddress, oRangeAddress)

Re: Copy/Transpose

PostPosted: Wed Aug 16, 2017 8:52 pm
by Zizi64
Here is an example code with usage of the Dispatcher (Recorded macro):

http://140.211.11.67/en/forum/viewtopic ... 9&p=286397


or:

https://ask.libreoffice.org/en/question ... o-problem/

Re: Copy/Transpose

PostPosted: Thu Aug 17, 2017 6:29 am
by det
I looked through the recommended sites, but could not find how to transpose without a format. I guess I have to go through a loop and paste each cell separately.
I can do this (I have seen some code), then the final question becomes:
How do I paste without a format? Thanks for any help. Det

Re: Copy/Transpose

PostPosted: Thu Aug 17, 2017 7:28 am
by Zizi64
How do I paste without a format?


Code: Select all   Expand viewCollapse view
oCellRange.setDataArray(outDataArray)

The command setDataArray will not format the target cells.

Re: Copy/Transpose

PostPosted: Thu Aug 17, 2017 8:50 am
by Villeroy
copyTransposedRangeSelection.ods
Transpose cell values by Basic code
(20.08 KiB) Downloaded 87 times

Re: Copy/Transpose

PostPosted: Thu Aug 17, 2017 5:54 pm
by det
I entered your Line into the code but get error "Object Variable not set".
Here my Code again:
Code: Select all   Expand viewCollapse view
Sub Main
Dim oSheet
Dim oRangeAddress 'The range to copy
Dim oCellAddress 'Destination address
Dim oCellRange
Dim oCellCopyRange
Dim oCellPaste
oSheet = ThisComponent.Sheets(2)
'oCellCopyRange= oSheet.getCellRangeByPosition("0,0:3,3") does not work
oCellpaste=oSheet.getCellByPosition(4,4)
' oSheet.copyRange(oCellAddress, oRangeAddress)
oRangeAddress = oSheet.getCellRangeByName("A1").getRangeAddress()
oCellAddress = oSheet.getCellByPosition(2, 7).getCellAddress()
oCellRange= oSheet.getCellByPosition(2, 7)
  'xxxx The Copy is done here xxx
oCellRange.setDataArray(outDataArray) 
oSheet.copyRange(oCellAddress, oRangeAddress)
oSheet = ThisComponent.Sheets(3)
oCellAddress = oSheet.getCellByPosition(0, 9).getCellAddress()
oSheet.copyRange(oCellAddress, oRangeAddress)
End Sub

Re: Copy/Transpose

PostPosted: Thu Aug 17, 2017 5:59 pm
by Zizi64
Please use the CODE tag in this forum for the code lists, and please upload your ods document with the embedded macro code.

Re: Copy/Transpose

PostPosted: Thu Aug 17, 2017 9:20 pm
by det
Here is the Code
Code: Select all   Expand viewCollapse view
Codes.ods

Re: Copy/Transpose

PostPosted: Thu Aug 17, 2017 9:29 pm
by Zizi64
det wrote:Here is the Code
Code: Select all   Expand viewCollapse view
Codes.ods



:?: :?: :?: :?: :?: :crazy:

Re: Copy/Transpose

PostPosted: Thu Aug 17, 2017 9:30 pm
by det
Sorry, I just figured out how to attach a file. Here it is.
Det

Re: Copy/Transpose

PostPosted: Thu Aug 17, 2017 9:42 pm
by Zizi64
What is this???

When I wrote that "embed the code into a document", then I meant: Embed (copy) the code list into a Module of the Standard directory of the document. The "frames" in the Spreadsheet are unusable.


Basic Directiries and Modules.png

Re: Copy/Transpose

PostPosted: Thu Aug 17, 2017 9:45 pm
by RoryOF
I embedded your previous code into [|Code] tags. Write your code (or Paste it) into your posting, then either place [quote][code] [/code][/quote] tags about it, or if using the Full report or Post Reply screens, select it and press the Code button

Re: Copy/Transpose

PostPosted: Thu Aug 17, 2017 10:35 pm
by det
Sorry, I am Beginner. Does this work?

Re: Copy/Transpose

PostPosted: Fri Aug 18, 2017 1:40 am
by det
RoryOF wrote:I embedded your previous code into [|Code] tags. Write your code (or Paste it) into your posting, then either place
tags about it, or if using the Full report or Post Reply screens, select it and press the Code button

Thank you, i got a lot to learn here is a sample. I'll see if it worked.
Code: Select all   Expand viewCollapse view
Sub Main
Dim oSheet
Dim oRangeAddress 'The range to copy
Dim oCellAddress 'Destination address
Dim oCellRange
Dim oCellCopyRange
Dim oCellPaste
oSheet = ThisComponent.Sheets(2)
'oCellCopyRange= oSheet.getCellRangeByPosition("0,0:3,3")
oCellpaste=oSheet.getCellByPosition(4,4)
' oSheet.copyRange(oCellAddress, oRangeAddress)
oRangeAddress = oSheet.getCellRangeByName("A1").getRangeAddress()
oCellAddress = oSheet.getCellByPosition(2, 7).getCellAddress()
oCellRange= oSheet.getCellByPosition(2, 7)
  'xxxx The Copy is done here xxx
'oCellRange.setDataArray(outDataArray) 
oSheet.copyRange(oCellAddress, oRangeAddress)
oSheet = ThisComponent.Sheets(3)
oCellAddress = oSheet.getCellByPosition(0, 9).getCellAddress()
oSheet.copyRange(oCellAddress, oRangeAddress)
End Sub