How can I include the name of the tab in a header without typing .

[Guest]

I have 4 large workbooks. I have to print all of the pages in each monthly.
I would like to add the names of the tabs in individual headers for each tab.
Is there a formula I can use that will pick this up, or will I have to type
each one manually?

 

[Gord Dibben]

kasaz

Sub TabInFooter()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.PageSetup.RightHeader = ws.Name
Next ws
End Sub

If not familiar with VBA and macros, see David McRitchie's site for more on
"getting started".

http://www.mvps.org/dmcritchie/excel/getstarted.htm

In the meantime..........

First...create a backup copy of your original workbook.

To create a General Module, hit ALT + F11 to open the Visual Basic Editor.

Hit CRTL + R to open Project Explorer.

Find your workbook/project and select it.

Right-click and Insert>Module. Paste the code in there. Save the
workbook and hit ALT + Q to return to your workbook.

The macro can be assigned to a button or shortcut-key combo.

Could also be run as a Before_Print event.

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Sheets
ws.PageSetup.RightHeader = ws.Name
Next ws
End Sub

This code would go into the ThisWorkbook module.


Gord Dibben Excel MVP