Macro needed, that picks up value of one cell to select a row

My calc file contains two sheets: “daten” and “bearbeiten”. In sheet “bearbeiten” cell A1 shows a number, say 150. Cell B1 contains text. I need a macro, that will pick up the number found in A1, e.g. 150, and use that number as row number in sheet “daten”, i.e., copying the content of cell B1 to cell B150 in “daten”. Can anyone help?

Merry Christmas and a happy new year…

REM  *****  BASIC  *****
  Sub xCopy
  Dim oDoc as Object
  Dim oSheet1 as Object, oSheet2 as Object
  Dim oRange1 as Object, oRange2 as Object
  Dim sText as String	

	oDoc = ThisComponent
	oSheet1 = oDoc.Sheets.getByName("bearbeiten")
	oSheet2 = oDoc.Sheets.getByName("Daten")

	' Source Range
	oRange1 =oSheet1.getCellRangeByName("A1")

	' get Destination Range from Cell A1 in Sheet >>bearbeiten<<
	' oRange1.Value-1 = row counter || minus 1, because the row count starts by 0
	oRange2 =oSheet2.getCellByPosition(1,oRange1.Value-1)

	' write string from Sheet >>bearbeiten<< Cell B1 to Destination Range
	oRange2.String=oSheet1.getCellRangeByName("B1").String

    End Sub

Regards

Craig

Craig, the macro works beautifully. It is everything I wanted. Thank you so much.

Function calls can pass parameters. To control a Sub by variable values, I only know the way to use a fix range for the service. Feeding global variables of the module may also be a way. Otherwise everything has to be hard coded.
Thus I prefer functions over subs wherever possible (and if using custom code at all).

If you want a demonstration of the “Function-Do” fo the task under diskussion, please study the attached example. The functions contained therein were coded some time ago and were never used in production. The code is preliminary. Errors expected!

Thank you very much for your answer. It will be useful to me.