Hi all,
absolute noob on programming macros but I had some spare time. Took a while and I hope I understood what I’ve done so far. At least it works.
Below code is to create a copy of a worksheet (invoice…), name it, enter some data like date, invoice no etc.
The point where I’m stuck is where I want to reference some cells from the new sheet back into my overview (sheet 1).
What I need would (in my logic :knock: ) look like
oCell=oSheet.getcellbyposition (2,lastrow)
oCell.formula="='"Rechnung "Renr'.A1"
to reference cell A1 from the new sheet back into another sheet which seems to be a few quotation marks too many though.
Should happen after
rem ---enter invoice no
as there’s a few more cells to be referenced
Is there any way to reference with sheetname being a variable?
Any help is greatly appreciated
Cheers
Alex
PS: ignore German comments. They’re just for myself so I know what I’ve done in like 9 months time 
Sub NeueRechnung
oDoc=ThisComponent 'assigns the current document to the variable document
oSheets = oDoc.Sheets 'get the container of all Sheets
rem --- Renr=invoice no as Variable
rem ---define Renr
oSheet = oSheets.getByName("Rechnungen")
Controller=oDoc.getcurrentController
Controller.SetActiveSheet(oSheet)
Range=oSheet.getCellrangeByposition(1,2,1,600) 'B-Ende
curs=oSheet.createCursorbyrange(range)
curs.GotoEndOfUsedArea(false) 'zum Ende gehen
lastrow = Curs.RangeAddress.EndRow-1 'Ende bestimmen
Renr=oSheet.getcellbyposition (1,lastrow-1).value+1
rem ---copy Rechnung
oSheets.copyByName("Rechnung", "Rechnung "+Renr, oSheets.Count)
rem ---set created sheet active
oSheet = oSheets.getByName("Rechnung "+Renr)
Controller = oDoc.getcurrentController
Controller.SetActiveSheet(oSheet)
rem ---enter invoice no
oSheet=oDoc.Sheets.getByName("Rechnung "+Renr)
oCell=oSheet.getCellByPosition(2,14) 'C15
oCell.SetString(Renr)
rem ---enter date
oSheet=oDoc.Sheets.getByName("Rechnung "+Renr)
oCell=oSheet.getCellByPosition(0,8) 'A9
oCell.SetValue(DateValue (Now)) 'date
oCell.NumberFormat=81
rem ---add row in overview
oSheet = oSheets.getByName("Rechnungen")
Controller=oDoc.getcurrentController
Controller.SetActiveSheet(oSheet)
Range=oSheet.getCellrangeByposition(1,2,1,600)
curs=oSheet.createCursorbyrange(range)
curs.GotoEndOfUsedArea(false)
lastrow = Curs.RangeAddress.EndRow-1 'Ende bestimmen
oSheet.Rows.insertByIndex(lastrow,1) 'insert row
rem ---copy row
CopyRange = oSheet.getCellRangeByposition(0,lastrow+1,20,lastrow+1)
PasteCell = oSheet.getCellByposition(0,lastrow)
CopyAddress = CopyRange.RangeAddress
PasteAddress = PasteCell.CellAddress
oSheet.CopyRange(PasteAddress, CopyAddress)
rem ---enter invoice no
oCell=oSheet.getcellbyposition (1,lastrow)
oCell.string=Renr
End Sub