Macro to set header

[FGOMEZ]

I have a file with about 50 different sheets (Names),
I want to print them all, and need to have a header for every sheet, which
is the same name of the TAB, could somebody help me to get a macro to set
the header automatically on every sheet.

Thanks in advance for your help

 

[zackb]

Hi there,

Mabye you could make use of something like this ...

sub SetAllHeaders()
dim ws as worksheet
for each ws in thisworkbook.worksheets '*
ws.pagesetup.centerheader = ws.name
next ws
end sub

I'm guessing that you don't need any special formats of any kind for this
and you want it in the center.

*Assumes you ARE inserting this code into the workbook in which you will
perform the code. If not, change "thisworkbook" with "activeworkbook" and
ensure the code is run on the workbook that is active (the desired
workbook).

 

[Bob Phillips]

For Each sh In Activeworkbook.Worksheets
with sh
.Pagesetup.LeftHeader = sh.name
.printout
End With
Next sh

--

HTH

RP
(remove nothere from the email address if mailing direct)

 

[zackb]

Also note that this usually takes a while to loop through and change the
pagesetup of Excel worksheets. (Drives me crazy) So 50 sheets may take you
a while. If you want to know how far along you are, change your code to
something like this ...

sub SetAllHeaders()
dim ws as worksheet, lngWs as long
lngWs = thisworkbook.worksheets.count
for each ws in thisworkbook.worksheets '*
application.statusbar = "Working on " & ws.index & " of " & _
lngWs & " : " & format(ws.index / lngWs, "Percent")
ws.pagesetup.centerheader = ws.name
ws.printout '**
next ws
application.statusbar = false
end sub

You must remember to set the statusbar to False when done.
** Added; forgot in last post. Sorry.