worksheet tab

[AsquareDC]

i want to only show a part of the worksheet tab name in header. for e.g, I
have my worksheet named Annex I - Buildings but I want only Annex I to be
displayed.

How do I do that?

 

[Mike H]

Hi,

I'm not sure what you mean by 'in a header' but to display the 7 leftmost
characters of your worksheet name try this

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,7)

Adjust the last number 7 to display more or less characters

Mike

 

[AsquareDC]

thanks for your response.

I wanted the name of my worksheet to be displayed in the header by using
"&[Tab]". However I do not want all the name to be displayed and I was
thinking that there is a syntax that could be used in the header section that
allows for some of it to be truncated.

That is what I am looking for.

Hi,

I'm not sure what you mean by 'in a header' but to display the 7 leftmost
characters of your worksheet name try this

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,7)

Adjust the last number 7 to display more or less characters

Mike

i want to only show a part of the worksheet tab name in header. for e.g, I
have my worksheet named Annex I - Buildings but I want only Annex I to be
displayed.

How do I do that?

 

[Mike H]

The correct function to get worksheet name is CELL, did you try it?

thanks for your response.

I wanted the name of my worksheet to be displayed in the header by using
"&[Tab]". However I do not want all the name to be displayed and I was
thinking that there is a syntax that could be used in the header section that
allows for some of it to be truncated.

That is what I am looking for.

Hi,

I'm not sure what you mean by 'in a header' but to display the 7 leftmost
characters of your worksheet name try this

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,7)

Adjust the last number 7 to display more or less characters

Mike

i want to only show a part of the worksheet tab name in header. for e.g, I
have my worksheet named Annex I - Buildings but I want only Annex I to be
displayed.

How do I do that?

 

[Dave Peterson]

You'd have to use a macro (or do it manually).

This kind of macro would go in the ThisWorkbook module:

Option Explicit
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim myHeader As String
With Me.Worksheets("Annex I - Buildings")
myHeader = Mid(.Name, 1, Len(.Name) - Len("- buildings") - 1)
.PageSetup.CenterHeader = myHeader
End With
End Sub

thanks for your response.

I wanted the name of my worksheet to be displayed in the header by using
"&[Tab]". However I do not want all the name to be displayed and I was
thinking that there is a syntax that could be used in the header section that
allows for some of it to be truncated.

That is what I am looking for.

Hi,

I'm not sure what you mean by 'in a header' but to display the 7 leftmost
characters of your worksheet name try this

=MID(CELL("filename",A1),FIND("]",CELL("filename",A1))+1,7)

Adjust the last number 7 to display more or less characters

Mike

i want to only show a part of the worksheet tab name in header. for e.g, I
have my worksheet named Annex I - Buildings but I want only Annex I to be
displayed.

How do I do that?