Tab Names (XP)

Topic

New Reply

Hi All,

I am trying to get a list of all the tabs in a workbook on a single sheet. I’m looking for a function(s) that

a) tells me the number of tabs (including charts and any other types of tabs you can put in)
gives me the name of the tab if I give it the tab index [0, 1, 2, …, max of answer from a)]

Do these functions exist in excel or do I need to build them?

Reply | Quote

Replies

The free ASAP Utilities include a tool to “Create a clickable index sheet. This will work kinda like a menu. Very handy when your dealing with large files with a lot of charts and sheets.”

It isalso possible to create your own custom functions:

Function NumberOfSheets() As Long
NumberOfSheets = ActiveWorkbook.Sheets.Count
End Function

Function NameOfSheet(i As Long) As String
NameOfSheet = ActiveWorkbook.Sheets(i).Name
End Function

Use in a cell formula as
=NumberOfSheets()
=NameOfSheet(3)

If you store the functions in your Personal.xls:
=Personal.xls!NumberOfSheets()
=Personal.xls!NameOfSheet(3)

Reply | Quote

thx Hans but … bother bother bother …

ActiveWorkbook.Sheets(i).Name

This gives me the name of the tabs in display order but given that I am changing the order of the tabs all the time (see post 467891), how do I extract a list of sheet names that doesn’t change order (al la the sheet names in the VBA project listing)

Reply | Quote

You can create a sorted list:

Sub ListSheets()
Dim i As Long
Dim n As Long
n = ActiveWorkbook.Sheets.Count
For i = 1 To n
Range(“A” & i) = ActiveWorkbook.Sheets(i).Name
Next i
Range(“A” & 1 & “:A” & n).Sort Key1:=Range(“A1”)
End Sub

Reply | Quote

Yup – that should work assuming that the required order of my tab names is alphabetical. Hmmm – I’m thinking that I might hard code a desired order and then re-sort the tabs after my print job.

Summary: I want to be able to print the tabs in a user defined order, also with the option of excluding some tabs from the print job. When you select multiple tabs, they are printed in the order that they appear on screen. So, I’ve put in some code to move the tabs into my desired print order. The downside is that this also changes what is sheet(1), sheet(2), etc into that order.

So, current proposed solution is …

1. before print, save current tab order
2. rearrange tab order so that print order comes out correctly
3. print
4. rearrange tab order back to original

I’ll give this a whirl and see how it goes.

Reply | Quote

In a quick test, I found that changing the VBA sheet code names won’t change the print order. Feeding the sheet name array, in the desired order, to the Printout method doesn’t work, correct? I think you’ll have to develop something like:

Sheets(“Menu”).Move Before:=Sheets(1)
Sheets(“Exhibit1”).Move Before:=Sheets(2)

to change the sheet order and corresponding print order.

Reply | Quote

Attached is something I use when I plan to use the INDIRECT function of on a bunch of sheets. It’ll need a calling code such as:

Sub ListSheetNames()
If TypeName(Selection) “Range” Then Exit Sub
SelectListSheetNameForm.Show
End Sub

Reply | Quote