Macro to set headers for all worksheets in a workbook

bhrjohnson

Board Regular
Joined
Oct 8, 2004
Messages
56
I would like to create a macro that will set the following parameters for every WS in a WB....

Left header: blank
Right Header: [Name of worksheet]
Center Header: [description for 2-letter code - see notes below]

Center Header Notes: All but 4 of the WSs are titled with a 2-letter code (Ex: AB, AU, LV, etc.). The 2-letter codes correspond to descriptions which are listed in the WS titled "Code Guide". In the code guide WS column A contains the 2-letter code and column B contains each corresponding description. Ex:

BL Balanced Funds
AU Gold Funds
LV Large-Cap Value

What I am trying to do is add a center header just for the 46 or so worksheets that contain a 2-letter code. In the center header I want to see the description that corresponds to the worksheet title code.

Ex: The worksheet titled "BL" should have a center header that reads "Balanced Funds".


Anyone of you gurus able to figure that one out? I tried to tackle this about a year ago and posted the same topic but we couldn't get the center header to work.

Thanks,
Bill
 

Excel Facts

Links? Where??
If Excel says you have links but you can't find them, go to Formulas, Name Manager. Look for old links to dead workbooks & delete.
Bill

This worked for me on a mockup with a few sheets.
Code:
Dim ws As Worksheet
Dim rng As Range

    Set rng = Worksheets("Code Guide").Range("A1:B2") ' change the range as required here
    
    For Each ws In Worksheets
        If ws.Name <> "Code Guide" Then
            With ws.PageSetup
                .RightHeader = ws.Name
                .CenterHeader = Application.WorksheetFunction.VLookup(ws.Name, rng, 2, 0)
            End With
        End If
    Next ws
 
Upvote 0
I'm getting the error: "unable to get the VLookup property of the worksheetfuncion class"

Here's my code:

Sub Objective_Headers()

Dim ws As Worksheet
Dim rng As Range

Set rng = Worksheets("Code Guide").Range("A1:B45")

For Each ws In Worksheets
If ws.Name <> "Code Guide" Then
With ws.PageSetUp
.RightHeader = ws.Name
.CenterHeader = Application.WorksheetFunction.VLookup(ws.Name, rng, 2, 0)
End With
End If
Next ws
End Sub
 
Upvote 0
Have you checked that the sheet names match the values in the Code Guide?

Check for trailing/leading spaces.

By the way was that the problem you had before?
 
Upvote 0
The values do match and I do not see any trailing or leading spaces in the codes. The earlier code seemed to be doing something (ie - their was flashing of the WS's) but the headers did not get added in. Here's the earlier code:

Sub Label()
Dim ws As Worksheet
Dim wb As Workbook

Set wb = ActiveWorkbook

For Each ws In wb.Sheets
If Len(ws.Name) = 2 Then
With ws.PageSetup
.LeftHeader = ""
.CenterHeader = Application.WorksheetFunction.VLookup(ws.Name, Sheets("Code Guide").Range("A1:B45"), 2, False)
End With
End If
Next ws
End Sub
 
Upvote 0
Does the VLOOKUP work on a worksheet?

The code you have posted is fundamentally the same as mine.
 
Upvote 0
Try this somewhere in a cell.

=VLOOKUP("AU", 'Code Guide'!A1:B45, 2, 0)
 
Upvote 0
Well I honestly can't see how the VLOOKUP doesn't work in the code.

Have you checked the sheet name for the code guide?
 
Upvote 0

Forum statistics

Threads
1,213,526
Messages
6,114,122
Members
448,550
Latest member
CAT RG

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top