worksheet ? of ??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I know about page numbering in the header or footer, but I need some way to
put the worksheet number (if counted from left to right) and the total number
of worksheets in my workbook in cells on my worksheets.
Does anyone know how to program some funtions that I can put into a cell
that I choose so that these number will automatically be generated and
changed, even if I remove or add worksheet, or change the order of the
worksheets in the workbook (from left to right), without having to run a
macro all the time.

Chris Hofman
 
Add the following code to a standard module:

Function CountSheet()
Application.Volatile
i = 0
Total = ThisWorkbook.Worksheets.Count
For Each Sht In Worksheets
i = i + 1
If Sht.Name = ActiveSheet.Name Then Exit For
Next
CountSheet = i & " of " & Total
End Function


And use it in each sheet as follows:
=countsheet()

Manges
 
On second thoughts, try removing the line Application.Volatile from th
code, if you are not getting proper results.

Mangesh
 
How about a UDF?

Option Explicit
Function SheetsInfo(rng As Range) As String

Application.Volatile

Dim TotalSheets As Long
Dim ThisSheetNumber As Long

ThisSheetNumber = rng.Parent.Index
TotalSheets = Sheets.Count

SheetsInfo = Format(ThisSheetNumber, "#,##0") _
& " of " & Format(TotalSheets, "#,##0")

End Function

This includes non-worksheet sheets in the count (like charts and macro sheets).

Use it in a cell like:

=sheetsinfo(a1)
 
Dave,

Thank you very much, I think your function is working fine for my workbooks.
Usually the funtion would be in a worksheet and I would copy the worksheet as
many times that is needed, but that all works fine with your function.
Shifting the worksheets from left to right, adding a worksheet, deleting, it
is working great!

Thanks again, greetings from The Netherlands.

Chris Hofman
 
Mangesh,

As you can see in the thread, the answer of Dave Peterson was satisfactory.
I tried your function, but when I copy/add a worksheet (and that is what I
mostly have to do in my workbooks), the number of the worksheet doesn't
automatically change, allthough the total number does.

But thanks very much for your info, greetings from The Netherlands.

Chris Hofman
 
Hey Chris,

Thanks for the feedback. It was a learning for me too.
Me in Goa, India. Greetrings to you too.

Mangesh
 
Dave Peterson said:
How about a UDF?

Option Explicit
Function SheetsInfo(rng As Range) As String

Application.Volatile

Dim TotalSheets As Long
Dim ThisSheetNumber As Long

ThisSheetNumber = rng.Parent.Index
TotalSheets = Sheets.Count

SheetsInfo = Format(ThisSheetNumber, "#,##0") _
& " of " & Format(TotalSheets, "#,##0")

End Function

This includes non-worksheet sheets in the count (like charts and macro sheets).

Use it in a cell like:

=sheetsinfo(a1)
Click to expand...

What does this mean to the lay person? I just want the numbers to read "1 of
15"
 
This is a User Defined Function that allows a user to put a formula in a cell
that will return that string (# of #).

If you're want this in a header/footer (file|page setup in xl2003), then this
means nothing to you.
 
Back
Top