Ask Your Question
1

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

asked 2015-08-12 18:38:00 +0200

MikeHelps gravatar image

updated 2015-08-19 05:24:30 +0200

doug gravatar image

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

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
2

answered 2015-08-19 07:58:55 +0200

karolus gravatar image

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
edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2015-08-12 18:38:00 +0200

Seen: 6,863 times

Last updated: Aug 19 '15