Calc Macro Help Automatic Sheet Creation Error

[LittleBread]

Hi, I have a list of location that I want in separated sheets.

Sheet name “Locations”
Number of locations: 21.

Macro I tried that is inspired from a answer on this site:
(looking at the preview, my code post will look horrible and I have no idea why or how to fix it)

Sub Main
  Set thisBook = ThisComponent
  locationsSheet = thisBook.Sheets.getByName("Locations")

  i = 0
  Do
    c = locationsSheet.getCellByPosition(0, i)

    REM // Stop creating sheets for locations once we find a
    REM // row with no name.
    If c.Type = com.sun.star.table.CellContentType.EMPTY Then
      print "Exiting: Found row with no location name."
      Exit Do
    EndIf

    locationName = c.String
    sNew = thisBook.createInstance("com.sun.star.sheet.Spreadsheet")
    thisBook.Sheets.insertByName(locationName, sNew)
    i = i + 1

  REM // Sanity-check on looping forever. We don't expect 
  REM // to have over 1000 locations.
  Loop Until i > 300
End Sub

Error received when I run macro:

"BASIC runtime error
An exception occurred
Type: com.sun.uno.RunetimeException
Message: . "

Thread where I found the inspiring code. (I only change the variables)

Automatic sheet naming

I am an instructor who uses Calc as a grade book. I name the sheets with the student’s name. This is a tedious process and must be repeated every semester. I wonder if it would be possible to implement a function where the name of the sheets are taken from one place. Example: I have one sheet called “data” that contains the name of the classes as well as the name of the students. It would be awesome if it was possible to use the cells containing the student’s name as sheet names. (I guess this i…

Never ran a macro on calc before. Any help in understanding what I am doing wrong would be greatly appreciated!

[Ratslinger]

@LittleBread Just need to highlight the lines, then on toolbar click Formatting icon (1010 on it).

[Ratslinger]

@LittleBread This code works as shown without error. The purpose of the code is to create new sheets with the names listed in column A of the named sheet (here it is ‘Locations’). Is this what you are really looking for or something else?

[LittleBread]

@Ratslinger Well, thank you for saying the code work! It pushed me to look into why it would not work on my end!
I believe the problem is related to missing JRE on my computer. I have just downloaded Java SE Runtime Environment 8.(Hopefully it’s the right one!)

While it’s still not working atm, I may simply need to reboot. Sadly, I wont know tonight because its getting late and I have a big windows 10 update upon restart!
I will look into it further tomorrow! Hopefully that was the problem.

[Ratslinger]

Hello,

To make this easier, attached is a sample which creates the sheets. In addition to creating each, that name is also placed into cell A1 on its’ respective. This is noted in the code (three lines) and can be commented out if not wanted. To make running the macro easier a button on the Locations sheet will run the macro.

If you want to run a second time, the newly created sheets must first be deleted or you will generate an error.

Sample - LocationsSheets.ods

Beside using a push button, you can run a macro from the menu - Tools->Macros->Run Macro. Then select the macro library (left pane - where you put it - document typically), highlight the macro to run (right pane) and click Run. That’s it.

If this answers your question please tick the (upper left area of answer). It helps others to know there was an accepted answer.