Macro to copy and rename sheet, then use sheet name in cell reference

[MikeHelps]

This Excel Macro is broken in LibreOffice. It is supposed to create a button in the sheet, which when clicked creates a copy of the sheet, asks the user what to name the new sheet, and then inserts that name into a cell on the sheet. [I’ve had to create a simple way for semi-literate computer users to keep making new timesheets in one file.]

Sub CopySheet()
  Dim i As Integer, x As Integer
  Dim shtname As String
  
  i = Application.InputBox("How many timesheets to you need?", "Copy sheet", Type:=1)
  For x = 0 To i - 1
     Worksheets("Blank").Copy After:=Sheets(Sheets.Count)
     shtname = InputBox("Enter a date without slashes / ", "What day did you work?")
     ActiveSheet.Name = shtname
 Next x
 End Sub

Formula in sheet is also broken:

=RIGHT(CELL("filename"),LEN(CELL("filename"))-FIND("]",CELL("filename")))

should return the name of the sheet

[karolus]

the Formula is not broken, but Libreoffice has different pathsyntax than excel, so you need:

=MID(CELL("filename");FIND("#$";CELL("filename"))+2;99) 

dependend on your locale you maybe have to replace semicolon by Comma

The Excel-macro is also not “broken” but it is needless VBA-stuff which will never work in Libre - anyway I’ve translate it into Starbasic:

Sub CopySheet()

    doc = thisComponent
    sheets = doc.Sheets

    i = InputBox("How many timesheets to you need?", "Copy sheet")
    For x = 0 To int(i) - 1
        shtname = InputBox("Enter a date without slashes / ", "What day did you work?")
        sheets.insertNewByName(shtname, sheets.Count) 
    Next x
End Sub