Page 1 of 1

Copy/Transpose

Posted: 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

'     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

Posted: 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

Posted: 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

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

Code: Select all

oCellRange.setDataArray(outDataArray)
The command setDataArray will not format the target cells.

Re: Copy/Transpose

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

Re: Copy/Transpose

Posted: 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

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

Posted: 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

Posted: Thu Aug 17, 2017 9:20 pm
by det
Here is the Code

Code: Select all

Codes.ods

Re: Copy/Transpose

Posted: Thu Aug 17, 2017 9:29 pm
by Zizi64
det wrote:Here is the Code

Code: Select all

Codes.ods

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

Re: Copy/Transpose

Posted: 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

Posted: 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

Posted: 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

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

Re: Copy/Transpose

Posted: 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

Code: Select all

 
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

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