Now I am trying to copy values from cells in one sheet to cells in another sheet but I get a reference error in the target cells since the source cells contains formulas with references to other cells in that sheet.
I only want to paste the values, not formulas, does anyone have an idea?
Code: Select all
Sub Sort_copy_to_statistics
Dim LRowindex,LColindex,maxit,i,getcol,setcol as long
Dim oDoc As Object
dim oSheet as object
Dim oRange
Dim o
LColindex = LastColNum 'Call to function that gets last column index that has content
LRowindex = LastRowNum 'Call to function that gets last row index that has content
Print "Nummer "& LRowindex
oDoc = ThisComponent
oSheet = oDoc.Sheets().getByName("Inventering")
oRange = oSheet.getCellRangeByPosition(0,0,1,LRowindex)
ThisComponent.CurrentController.select(oRange)
o = ThisComponent.CurrentController.getTransferable()
oSheet = oDoc.Sheets().getByName("Veckostatistik")
oRange = oSheet.getCellRangeByPosition(0,0,1,LRowindex)
ThisComponent.CurrentController.select(oRange)
ThisComponent.CurrentController.insertTransferable(o)
'Copy to statistik
maxit = 0
i = 0
Do while maxit < LColindex
oSheet = oDoc.Sheets().getByName("Inventering")
getcol = 8+6*i
setcol = 2+i
oRange = oSheet.getCellRangeByPosition(getcol,0,getcol,LRowindex)
ThisComponent.CurrentController.select(oRange)
o = ThisComponent.CurrentController.getTransferable()
oSheet = oDoc.Sheets().getByName("Veckostatistik")
oRange = oSheet.getCellRangeByPosition(setcol,0,setcol,LRowindex)
ThisComponent.CurrentController.select(oRange)
ThisComponent.CurrentController.insertTransferable(o)
i= i+1
maxit = getcol +2
Loop
End Sub
Everything works except I get the cell references that doesn't work on the new sheet.
I would really appreciate some help.
Cheers!