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
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
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
Re: Copy/Transpose
Posted: Thu Aug 17, 2017 9:29 pm
by Zizi64
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.
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
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