Unable to run a simple code

[michel.azzi]

Good day to all, please i am kind of new to libreOffice and i am trying to run a piece of code that i previously tried on Excel and worked. but i cant seem to correct the problem with Libreoffice. The task is simple, i am trying to assign a copy action to a button, but the target cell will be extracted from the value inside another cell ( a picture is attached). the VBA code that worked perfectly in excel and showed me errors in Libreoffice was:

Private Sub CommandButton1_Click()
With Worksheets("Sheet1")
  .Range("K10").Copy Destination:=.Range(.Range("K6").Value)
End With
End Sub

[Lupp]

Your code clearly is “very Excel-VBA”. Since I don’t feel sure about what you actually meant I cannot edit your post using the tool for posting code without a risk to introduce errors.
Concerning general issues with moving VBA code to LibreOffice, you may read the recent thread CalcBASIC: How to move cursor to 4 directions?.

[Lupp]

Well, meanwhile I verified the code and edited the question to make it (as I think) a bit better readable.
It uses typical VBA constructs an syntax and cannot run in native LibreOffice Basic.
However, it was correctly executed under
Option VBAsupport 1 .

[anon73440385]

Hello,

don’t assume all VBA code is working (VBA has limited support in LibreOffice) and at least you would need to add:

Option VBASupport 1 at the beginning of your Basic Modul (i.e. before any Sub or Function statement).

(May be it doesn’t work anyway, though you made the setting. I did not test, but without that option it definitely will not)

Hope that helps.

[Lupp]

After reading my answer to the above linked question you may sudy this attached example.
Along with the VBA code only executable under the mentioned option it contains a native “implementation” of the proceeding:

Sub commandButton2_Click_nativeLibreOfficeBasicWithAPI
theSheet    = ThisComponent.Sheets.getByName("Sheet1")
sourceRange = theSheet.getCellRangeByName("K10")
paramCell   = theSheet.getCellRangeByName("K6")
targetCell  = theSheet.getCellRangeByName(paramCell.String)
theSheet.CopyRange(targetCell.CellAddress, sourceRange.RangeAddress)
End Sub

[michel.azzi]

Good day, thank you for this great direction. i Still have 2 main concerns that i would like to have your help with:
1- in one of the cases, the “sourceRange” is a cell that contains a formula that i need only to copy the answer. what i am facing is this code is copying the formula inside the cell not the answer itself.

the code i am using is this one:

Sub commandButton2_Click_nativeLibreOfficeBasicWithAPI
theSheet = ThisComponent.Sheets.getByName(“Sheet1”)
sourceRange = theSheet.getCellRangeByName(“K10”)
paramCell = theSheet.getCellRangeByName(“K6”)
targetCell = theSheet.getCellRangeByName(paramCell.String)
theSheet.CopyRange(targetCell.CellAddress, sourceRange.RangeAddress)
End Sub

2- i see in the code that we are calling the sheet involved by its name, i think if i duplicate the sheet and change its name, i suppose that the code will not run again because the sheet name is changed. is there anyway to automatically upgrade the code so it fits this need to duplicate sheets ?