identify sheet number in header?

[Steve]

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.

 

[Eduardo]

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]

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

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)

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.

 

[Gord Dibben]

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

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

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)

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.

 

[Steve]

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.

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

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

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)

:

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.

 

[Gord Dibben]

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

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.

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

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

:

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)

:

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.