Header and Footer

Can someone help me in regards to the Header in an excel workbook. I have a
workbook that has 10 worksheets (10 different branches). I would like the
header on all of them to read something like this Statistic Reports for
"branch" I would like the branch to be automatically inserted and then I also
have a summary sheet I would like all of the branch names to be included in
this sheet. Can someone please help or tell me a code I could use

Where is this branch name supposed to come from? Sheet tab name? A cell on
the sheet? Or is that part of the question you're asking?

The Custom header/footer dialog has an icon to automatically insert the name
on the sheet's tab into the header, so that's probably the easiest answer for
the various sheet's headers.

The answer for automatically coming up with division names is almost not
automatic - when you see the formula I think you'll understand why. But as
with the answer to the first half, it depends on the sheet's names being the
Division names. Here's a formula that will give you the tab text (sheet
name) from Sheet1:

=RIGHT(CELL("filename",Sheet1!A1),LEN(CELL("filename",Sheet1!A1))-FIND("]",CELL("filename",Sheet1!A1)))

You'll notice the name of the sheet you're looking for is typed 3 times in
the formula itself. But this code, put on the "table of contents" sheet will
place the names of all the other sheets in the workbook on it beginning at
A1. The code runs when that sheet is selected (Activated):
Private Sub Worksheet_Activate()
Dim AnySheet As Worksheet
Dim RowOffset As Integer

For Each AnySheet In Worksheets
If AnySheet.Name <> ActiveSheet.Name Then
Range("A1").Offset(RowOffset, 0) = AnySheet.Name
RowOffset = RowOffset + 1
End If
Next

End Sub

This page gives the gory details of how to insert code attached to a
worksheet event: http://www.jlathamsite.com/Teach/WorksheetCode.htm in case
you need that information.


"Need Help" wrote:

> Can someone help me in regards to the Header in an excel workbook. I have a
> workbook that has 10 worksheets (10 different branches). I would like the
> header on all of them to read something like this Statistic Reports for
> "branch" I would like the branch to be automatically inserted and then I also
> have a summary sheet I would like all of the branch names to be included in
> this sheet. Can someone please help or tell me a code I could use