I have a workbook with 41 sheets. When printed out each sheet may be more
than one page. However, each sheet is a separate tab in a notebook. So I
wonder if there's a way to set up a header in such a way that the sheet
(i.e., tab) number prints.

Hi,
In A1 enter this formula it will give the tab name then you will use it in
yours prints
=MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255)

"Steve" wrote:

I have a workbook with 41 sheets. When printed out each sheet may be more
than one page. However, each sheet is a separate tab in a notebook. So I
wonder if there's a way to set up a header in such a way that the sheet
(i.e., tab) number prints.

I don't want the sheet name. I want the sheet number...1, 2, 3, etc.

"Eduardo" wrote:

Hi,
In A1 enter this formula it will give the tab name then you will use it in
yours prints
=MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255)

"Steve" wrote:

I have a workbook with 41 sheets. When printed out each sheet may be more
than one page. However, each sheet is a separate tab in a notebook. So I
wonder if there's a way to set up a header in such a way that the sheet
(i.e., tab) number prints.

You want the codename/number of the sheet?

Sub sheetnumber()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.PageSetup.CenterFooter = ws.CodeName
Next
End Sub


Gord Dibben MS Excel MVP

On Mon, 8 Dec 2008 12:57:00 -0800, Steve
wrote:

I don't want the sheet name. I want the sheet number...1, 2, 3, etc.

"Eduardo" wrote:

Hi,
In A1 enter this formula it will give the tab name then you will use it in
yours prints
=MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255)

"Steve" wrote:

I have a workbook with 41 sheets. When printed out each sheet may be more
than one page. However, each sheet is a separate tab in a notebook. So I
wonder if there's a way to set up a header in such a way that the sheet
(i.e., tab) number prints.

That's what I thought I wanted. However, after I created all of the
worksheets I rearranged them. So the codenames aren't sequential. For
example the first sheet in the workbook is actually sheet41. Maybe it's not
possible to do what I want to do. Thanks for trying, though.

"Gord Dibben" wrote:

You want the codename/number of the sheet?

Sub sheetnumber()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.PageSetup.CenterFooter = ws.CodeName
Next
End Sub


Gord Dibben MS Excel MVP

On Mon, 8 Dec 2008 12:57:00 -0800, Steve
wrote:

I don't want the sheet name. I want the sheet number...1, 2, 3, etc.

"Eduardo" wrote:

Hi,
In A1 enter this formula it will give the tab name then you will use it in
yours prints
=MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255)

"Steve" wrote:

I have a workbook with 41 sheets. When printed out each sheet may be more
than one page. However, each sheet is a separate tab in a notebook. So I
wonder if there's a way to set up a header in such a way that the sheet
(i.e., tab) number prints.

You want the sheets' position numbers?

Sub sheetnumber()

Dim I As Integer, J As Integer

For I = 1 To Sheets.Count
For J = I To Sheets.Count
Sheets(J).PageSetup.CenterFooter = "sht " & I

Next J
Next I
End Sub


Gord

On Mon, 8 Dec 2008 13:55:01 -0800, Steve
wrote:

That's what I thought I wanted. However, after I created all of the
worksheets I rearranged them. So the codenames aren't sequential. For
example the first sheet in the workbook is actually sheet41. Maybe it's not
possible to do what I want to do. Thanks for trying, though.

"Gord Dibben" wrote:

You want the codename/number of the sheet?

Sub sheetnumber()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.PageSetup.CenterFooter = ws.CodeName
Next
End Sub


Gord Dibben MS Excel MVP

On Mon, 8 Dec 2008 12:57:00 -0800, Steve
wrote:

I don't want the sheet name. I want the sheet number...1, 2, 3, etc.

"Eduardo" wrote:

Hi,
In A1 enter this formula it will give the tab name then you will use it in
yours prints
=MID(CELL("filename",C1),FIND("]",CELL("filename",C1))+1,255)

"Steve" wrote:

I have a workbook with 41 sheets. When printed out each sheet may be more
than one page. However, each sheet is a separate tab in a notebook. So I
wonder if there's a way to set up a header in such a way that the sheet
(i.e., tab) number prints.