Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Page numbers
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). |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Page numbers
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 On Thu, 31 Jan 2008 14:14:02 -0800, av8torsgal wrote: 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). |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Page numbers
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" wrote: 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 On Thu, 31 Jan 2008 14:14:02 -0800, av8torsgal wrote: 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). |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Page numbers
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 On Fri, 1 Feb 2008 13:26:36 -0800, av8torsgal wrote: 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" wrote: 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 On Thu, 31 Jan 2008 14:14:02 -0800, av8torsgal wrote: 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). |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Page numbers on copies of a single page worksheet | Excel Discussion (Misc queries) | |||
Add page numbers to multiple worksheets without changing page setu | Excel Discussion (Misc queries) | |||
Page numbers - not starting from the first page | Excel Discussion (Misc queries) | |||
Page Break Page Numbers | Excel Discussion (Misc queries) | |||
consecutive page numbers of the same page | Excel Discussion (Misc queries) |