Ask Your Question

Calc Macro Help Automatic Sheet Creation Error

asked 2018-02-14 05:28:28 +0100

LittleBread gravatar image

updated 2018-02-14 05:32:02 +0100

Ratslinger gravatar image

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
    c = locationsSheet.getCellByPosition(0, i)

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

    locationName = c.String
    sNew = thisBook.createInstance("")
    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: Message: . "

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

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

edit retag flag offensive close merge delete


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

Ratslinger gravatar imageRatslinger ( 2018-02-14 05:35:02 +0100 )edit

@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?

Ratslinger gravatar imageRatslinger ( 2018-02-14 05:50:07 +0100 )edit

@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.

LittleBread gravatar imageLittleBread ( 2018-02-14 06:42:14 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2018-02-14 06:18:26 +0100

Ratslinger gravatar image

updated 2018-02-14 06:24:06 +0100


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.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2018-02-14 05:28:28 +0100

Seen: 21 times

Last updated: Feb 14 '18