Solved: Print Page Numbering by Tab index in Footer

[sxschech]

I looked through the forum and saw some tips that may do some of what I would like, but not quite sure how to implement. I have a file with about 20 tabs, each with there own name (Notes, TableOfContents, SanFran, LA, Sact, Portland, etc) and would like to have the page number in the page footer use the tab number as the page number (+ or - the tabs that won't ever be printed, for example if I want Tab 3 "SanFran" to be showing Page 1 in the footer, then it would be 3-2=1, and LA would be 4-2=2, etc). One tip had mentioned to use the default page numbering scheme for the excel footer and then highlight all the pages to be printed and then this would number the pages consecutively, however, if I only need to print a specific page, this would print out as page 1 rather than page 15. The other reason I was hoping to be able to get the page number by the tab index is that if I move the tab to another location, add a new tab, or delete a tab, the numbering would adjust accordingly. If I need to use vba or macro, how could I let other users know how to run it without having to have a button or message on each tab, since the Notes or Table of Contents page may not always be visible on the list of tabs. Thank you.

[Ken Puls]

Hi there, and welcome to VBAX.

Yep, you'll need a macro for this. The code below will need to go into the ThisWorkbook module of the workbook you want it to run in:

[vba]Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
Dim lSht As Long

For Each ws In ThisWorkbook.Worksheets
Select Case ws.Name
Case Is = "Sheet1", "AnotherSheet"
'Do Nothing
Case Else
'Add to page count
lSht = lSht + 1

'Update the footer
With ws
With .PageSetup
.RightFooter = "Page " & lSht
End With
End With
End Select
Next ws
End Sub[/vba]

Once you've got it pasted in to the correct module, you'll need to update Sheet1 and AnotherSheet to names of the worksheets that you do NOT want numbered. And if there are any more, then add them to the string like AnotherSheet. I.e. ,"worksheet3","yet another worksheet","and this one too"

This macro will automatically run before the workbook is printed and renumber the sheets for you.

HTH,

[sxschech]

Hi Ken Puls,

Thank you so much for your response and great help. The code worked perfectly and your explanation on how to install and implement it made it easy to use.

As this is my first post here, would like to know, do I click on the rate thread and choose excelent to indicate that your response was appreciated or is that for a different purpose?

[Ken Puls]

Hi there,

Honestly, the thanks is enough. You could rate the thread if you like, but it's hardly necessary. One thing that is encouraged, though, is to mark the thread solved. (On your Thread Tools menu there is a "Mark Thread Solved" selection.)

Cheers,