Ask Your Question
1

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

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

MikeHelps gravatar image

updated 2020-08-23 17:15:24 +0100

Alex Kemp 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 reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2020-08-23 17:15:31.122018

1 Answer

Sort by » oldest newest most voted
2

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

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

Question Tools

1 follower

Stats

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

Seen: 7,749 times

Last updated: Aug 19 '15