Ask Your Question

Automatic sheet naming. [closed]

asked 2013-01-20 01:23:43 +0100

this post is marked as community wiki

This post is a wiki. Anyone with karma >75 is welcome to improve it.

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 is more of a feature request than anything.

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 2015-10-20 13:42:59.342085


To qubit. Thank you so much. I shall use that.

Olle gravatar imageOlle ( 2013-01-20 16:51:50 +0100 )edit

@Olle, you're quite welcome. If you test out the code and it works for you, please mark the answer as 'correct'.

qubit gravatar imagequbit ( 2013-01-20 21:20:53 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2013-01-20 07:03:16 +0100

qubit gravatar image

updated 2013-01-20 07:05:26 +0100

Hi @Olle,

Given that you'd probably want the sheet to remain even if you (accidentally) deleted a student's name from a spreadsheet cell, I think that some form of automation might be best for you.

Here's a quick macro that you can adapt to your needs. I made a couple of simple assumptions

  • Students are listed in a sheet named 'Students'
  • Column 'A' contains students names
  • There are no empty cells before or between student names
  • Student names are unique

    Sub Main
      Set thisBook = ThisComponent
      studentsSheet = thisBook.Sheets.getByName("Students")
      i = 0
        c = studentsSheet.getCellByPosition(0, i)
        REM // Stop creating sheets for students once we find a
        REM // row with no name.
        If c.Type = Then
          print "Exiting: Found row with no student name."
          Exit Do
        studentName = c.String
        sNew = thisBook.createInstance("")
        thisBook.Sheets.insertByName(studentName, sNew)
        i = i + 1
      REM // Sanity-check on looping forever. We don't expect 
      REM // to have over 1000 students.
      Loop Until i > 1000
    End Sub
edit flag offensive delete link more

Question Tools


Asked: 2013-01-20 01:23:43 +0100

Seen: 690 times

Last updated: Jan 20 '13