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 |
Header and Footer
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("filena me",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 |
All times are GMT +1. The time now is 03:10 PM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com