Custom Header (1 Viewer)

[CharlesWilliams]

Hello Excel Experts,

I'm wondering if theere is any way to do something...

I have a spreadsheet (Attached) where, when I print a paticular month, I would like to...

A) Show the salesman's name from the workbook name (Flinstone) in the left header.

B) Show the month from the spreadsheet's tab (January, February,...) and the year from the workbook name (2012) in the right header.

I would like them to be bold and underlined also.

I know this asking alot of Excel but I would love it if this could be done automaticlly.

Hopefully I've explained it clearly.

Thanks for anyhelp you may be able to provide.

- Charles

 

[Trevor G]

How have you got on? Try this out in a copy of your workbook.

Select your first sheet then use Page Setup and Select Headers/Footers then use the Icons to place in the workbook name (Left Section), Select the Tab Icon to show the Worksheet Name (Center Section) add the year in the right section. Format them accordingly.

Once done then come out of Print Preview, Select all the Sheets and then use Page Setup and then click OK (No need to do anything else). Because you have them grouped it then applies the same Header to all the worksheets.

I hope that helps.

 

[CharlesWilliams]

Trevor,

I should have prefaced my original message with i'm not familiar with VBA in Excel. So in saying that...

I created a macro to insert the Header (left and right) as needed. The only problem with this solution (if you want to call it that) is that you have to run it on each sheet.

I'm kind of thinking I should be able to adjust the macro to run all the sheets in one run.

I'll try your solution out before messing with my macro.

Thank You Trevor.

 

[Trevor G]

If you want the code for each worksheet it would be something along the lines of the following:

Sub PageSetupArraySheets()
Dim wsH As Worksheet
'Loop through each sheet
'Adjust Page Setup
For Each wsH In ActiveWorkbook.Worksheets
With wsH.PageSetup
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Zoom = 100
.LeftHeader = "A" 'Your code goes here
.CenterHeader = "B" 'Your code goes here
.RightHeader = "C" 'Your code goes here

End With
Next wsH
End Sub

 

[CharlesWilliams]

OK Trevor,

Here's how I changed the code:
Sub PageSetupArraySheets()
Dim wsH As Worksheet
'Loop through each sheet
'Adjust Page Setup
For Each wsH In ActiveWorkbook.Worksheets
With wsH.PageSetup
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Zoom = 100
LeftHeaderSetup = InputBox("Enter a salesman (lamb,Silver, ect.)", "Salesman's Name")
.LeftHeader = "LeftHeaderSetup" 'Your code goes here
.CenterHeader = "&""Times New Roman,Bold""&12&USALESMAN COMMISSION"
RightHeaderSetup = InputBox("Enter a month and year (July 2012)", "Month And Year")
.RightHeader = "RightHeaderSetup" 'Your code goes here

End With
Next wsH
End Sub

This causes 2 problems as follows:
1) It makes the header to wide for the page.

2) It now prompts me for the header left & right for each sheet. (AnitProductive)

The way it should be is to prompt me for the salesmans name once and fill it in for all the sheets (Left).

It can get the month from the tab from each sheet and prompt once for the year and fill in the sheets accordinly.

You can see what i'm talking about in the attached spreadsheet on the January tab.

thank you for all the help you can provide.

- Charles

 

[Trevor G]

Charles nice to see you had a go at adding to the code.

If you want to work with Inputbox's for all sheets you need to take that outside the With Statement and declare it, the with statement is moving to each sheet so you would get an inputbox for each sheet.

When you work inside a with statement you also need to place in a fullstop at the beginning of the line, and you should get a drop down list to indicate what you want to select.

I have adjusted the code and tested it, so you enter the sales person name and year then it does everything else for you. Have a happy New Year and see you on the board Next Year.

Sub PageSetupArraySheets()
Dim wsH As Worksheet
'Loop through each sheet
'Adjust Page Setup
Dim inp As String 'Added to use Inputbox
Dim inpYear As String 'Added to use Inputbox
inp = InputBox("Enter a salesman (Lamb,Silver, etc.)", "Salesman's Name")
inpYear = InputBox("Enter a Year like 2011 or 2012", "Month And Year")

For Each wsH In ActiveWorkbook.Worksheets
With wsH.PageSetup
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Zoom = 100
.LeftHeader = inp
.CenterHeader = "&""Times New Roman,Bold""&12&USALESMAN COMMISSION"
.RightHeader = wsH.Name & " " & inpYear
End With
Next wsH
End Sub

 

[CharlesWilliams]

Thanks bunches Trevor,

I'll give it a try. Happy New Year!