I have had this macro for a while now and never had a problem since I was pasting just text information from a cell range to another sheet. However, now I have a formula in that cell range that copes over, and when the cell range copies to the new sheet the formula is pasted instead of the value. I can solve this if I manually paste it as a “paste special”.
Sub Copy2FirstBlankCell()
Dim oDoc As Object
Dim oSheet As Object
Dim SourceAddress As New com.sun.star.table.CellRangeAddress
Dim DestinationAddress As New com.sun.star.table.CellAddress
Dim DestinationCell As Object
Dim r As Long
Dim c As Integer
oDoc = ThisComponent
oSheet = oDoc.getSheets().getByIndex(0)
'CellrangeAddress of Sheet1.B1
SourceAddress.Sheet = 0
SourceAddress.StartColumn = 0
SourceAddress.StartRow = 0
SourceAddress.EndColumn = 5
SourceAddress.EndRow = 0
'CellAddress of Sheet1.A1
r = 0
c = 0
DestinationAddress.Sheet = 1
DestinationAddress.Column = c
DestinationAddress.Row = r
DestinationCell = oDoc.getSheets().getByIndex(DestinationAddress.Sheet).getCellByPosition(c,r)
Do While DestinationCell.getType() <> com.sun.star.table.CellContentType.EMPTY And r < oSheet.getRows().getCount()
r = r + 1
DestinationAddress.Row = r
DestinationCell = oDoc.getSheets().getByIndex(DestinationAddress.Sheet).getCellByPosition(c,r)
Loop
If DestinationCell.getType() = com.sun.star.table.CellContentType.EMPTY Then
oSheet.copyRange(DestinationAddress,SourceAddress)
Else
Msgbox("Ran out of rows.")
EndIf
I believe the solution is at the botom of the code on the Loop area on:
oSheet.copyRange(DestinationAddress,SourceAddress)
But I’m not sure what to change it to