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:
' 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)
Last edited by RoryOF on Thu Aug 17, 2017 9:21 pm, edited 1 time in total.
Reason:Added [Code] tags [RoryOF, Moderator]
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
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
The command setDataArray will not format the target cells.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
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
Last edited by RoryOF on Thu Aug 17, 2017 9:22 pm, edited 1 time in total.
Reason:Added [Code] tags [RoryOF, Moderator]
Please use the CODE tag in this forum for the code lists, and please upload your ods document with the embedded macro code.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
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.
Tibor Kovacs, Hungary; LO7.5.8 /Win7-10 x64Prof.
PortableApps/winPenPack: LO3.3.0-7.6.2;AOO4.1.14
Please, edit the initial post in the topic: add the word [Solved] at the beginning of the subject line - if your problem has been solved.
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
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