setting up a page numbering format

[Lenny]

I want to set up a numbering system for an excel 2003 form. I DON'T want to
use page and page of found under header/footer because I don't want to have
these items in the margins, but within the body of the form itself.

The file is set up with sheet one (main form) followed by a continuation
sheet which can be copied to additional tabs creating many continuation
sheets. Using an IF formula, is there a way to reference the (sheet) cell
and (of) cell on the first sheet (tab) and use it to sequence the rest of the
sheets in the file?

Example:
Sheet 1 is the first sheet of 8 total sheets (tabs) in the file.....
Sheet 1 is sheet 1 of 8, sheet 2 is 2 of 8, sheet 3 is 3 of 8..... etc.

Later versions of excel may have this feature now built in, but not in 2003.
Searching thru HELP in excel is getting me nowhere.

Any enlightenment on this subject would be greatly appreciated.... Lenny

Regards

 

[BSc Chem Eng Rick]

Hi Lenny

I think the routine below does what you specify. It captures the
"SheetActivate" event for the workbook and renumbers the tabs as you require.
Make sure you paste this into the code for the Workbook, NOT in a standalone
module.

Private Sub Workbook_SheetActivate(ByVal Sh As Object)
Dim MySheet As Worksheet, WBSheets As Sheets
Dim i As Integer
Application.ScreenUpdating = False
Set WBSheets = ThisWorkbook.Worksheets
i = 0
For Each MySheet In WBSheets
i = i + 1
MySheet.Name = CStr(i) & " of " & CStr(WBSheets.Count)
Next MySheet
Application.ScreenUpdating = True
End Sub

If this helps, please click "Yes"
<><><><><><><><><><><>