Ask Your Question

How to Extract a list of sheet names in calc HELP

asked 2016-03-05 03:22:00 +0200

King_ZZ gravatar image

Ideally I want to have some kind of formula that will list all my sheets.
If possible to be dynamic so when I add / rename / move a sheet to a different location the list would update.

I have found these two things one is using VBA and the other is without.
I cannot get either to work as for the VBA I do not know how calc references sheets,
in th VBA code it is workbook.

These are both for MS Office and I do not know how to convert them

Extract a list of sheet names in your workbook without using a macro
Go to FORMULAS tab and click on Define Name. Fill in the box as shown below:
image description
The function used is “=replace(get.workbook(1),1,find("]",get.workbook(1))," ")”

Then wherever you want the list of sheet names, simply type in this formula: =INDEX(sheetlist,ROW())

It will show you names of the sheets in each cell. Drag the formula down to complete the list of sheets in the workbook.

This is the VBA way
You can do this only with VBA code. There is no worksheet function to get sheet names. (although there is I found one listed above)

Sub ListSheetNames()
Dim R As Range
Dim WS As Worksheet
Set R = ActiveCell
For Each WS In ThisWorkbook.Worksheets
R.Value = WS.Name
Set R = R(2, 1)
Next WS
End Sub

Select the cell where the list should start and run the code above.
I also found this VBA

Sub ListWorkSheetNamesNewWs()
Dim xWs As Worksheet
On Error Resume Next
Application.DisplayAlerts = False
xTitleId = "KutoolsforExcel"
Application.Sheets.Add Application.Sheets(1)
Set xWs = Application.ActiveSheet
xWs.Name = xTitleId
For i = 2 To Application.Sheets.Count
xWs.Range("A" & (i - 1)) = Application.Sheets(i).Name
Application.DisplayAlerts = True
End Sub

edit retag flag offensive close merge delete


I can get sheet location with SHEET and how many sheets in my doc with SHEETS but I cannot see anyway of using that information yet

King_ZZ gravatar imageKing_ZZ ( 2016-03-05 03:26:56 +0200 )edit

2 Answers

Sort by » oldest newest most voted

answered 2016-03-05 10:22:33 +0200

karolus gravatar image


First - All this stuff is useless use of coding, because there are smarter ways to navigate and handle with sheets.

  • →Navigator
  • the Sheettabs

Last not least, the messy VBA-shit above can be simply replaced by UNO-Api-methods:

# python example to list Sheetnames below current selected cell

def sheetnames():
    doc = XSCRIPTCONTEXT.getDocument()
    sel = doc.CurrentSelection
    sheet = sel.Spreadsheet
    c = sheet.createCursorByRange( sel )
    snames = tuple( zip( doc.Sheets.ElementNames ) )
    c.collapseToSize( 1, len( snames ) )
    c.setFormulaArray( snames )
edit flag offensive delete link more


Did you notice that the code posted in my answer to another question linked in here not is useless but concise and effective and actually offering a solution to the task described in the subject of this thread?
There are advantages in not using an array for the purpose.

Lupp gravatar imageLupp ( 2016-03-05 16:26:32 +0200 )edit

@Lupp I notice your Answer actuallly after posting my answer.
I mean "useless" generally in terms of "cost-benefit ratio" here also for the python-snippet above
I mean "useless" because its annoying to see any other day some VBA -code which, -surprise,surprise- don't work.

karolus gravatar imagekarolus ( 2016-03-05 16:58:30 +0200 )edit
Lupp gravatar imageLupp ( 2016-03-05 17:58:43 +0200 )edit

answered 2016-03-05 09:48:38 +0200

Lupp gravatar image

Refer my answer here.

edit flag offensive delete link more
Login/Signup to Answer

Question Tools

1 follower


Asked: 2016-03-05 03:22:00 +0200

Seen: 1,769 times

Last updated: Mar 05 '16