identify sheet number in header?
 

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.

identify sheet number in header?
 

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.

identify sheet number in header?
 

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.

identify sheet number in header?
 

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.

identify sheet number in header?
 

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.

identify sheet number in header?
 

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.