Page numbers

[av8torsgal]

Is there a way to identify the page number of a tabbed worksheet without
manually inputting this information? I want the tabs to consecutively number
and adjust for deleted and/or added tabs. For example, I have a document
with multiple tabs that are named using text. When I am viewing or working
on a certain tab, I want to know the tab number in addition to the name of
the tab. I need this information when viewing or working on a tab rather
than when printing (so headers/footers will not work in this case).

 

[Gord Dibben]

An Excel document is a Workbook.

Workbooks can have muliple worksheets.

Each worksheet can have multiple "pages" when printing.

What do you consider as a "tab"?

A worksheet or a printed "page"?

It seems to me you use the word "tab" to mean both.

Tabs(worksheets) don't have numbers except for a VBA codename number.

If you want a list of your sheetnames and corresponding codenames, run this
macro on a new sheet.

Sub CreateListOfSheetsOnFirstSheet()
Dim ws As Worksheet
For i = 1 To Worksheets.Count
With Worksheets(1)
Set ws = Worksheets(i)
.Cells(i, 1).Value = ws.Name
.Cells(i, 2).Value = ws.CodeName
End With
Next i
End Sub

If something else, wait for other responses or post back with a clearer
description of "tabs" and "numbers" and "pages" to help me out.


Gord Dibben MS Excel MVP

 

[av8torsgal]

Upon starting a new workbook, the names for each worksheet default to "sheet
1", "sheet 2" and so on. I have renamed the sheets but still need to know
what the sheet number is; i.e. if the workbook has 50 sheets and I am working
on sheet 32 (which I have named "green") how can I identify that it is sheet
32?

Further, I need the sheet numbers to automatically update when adding or
deleting a sheet.

 

[Gord Dibben]

To see the codenames(numbers) of each sheet, run the macro provided below to get
a list of your names and codenames.

To see the codename of any sheet this UDF will give you that.

Function Sheetnum(Optional ByVal rng As Range) As String
Application.Volatile
If rng Is Nothing Then Set rng = Application.Caller
Sheetnum = rng.Parent.CodeName
End Function

On any sheet in any cell enter =Sheetnum()


Gord