Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to set header
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 |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to set header
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). -- Regards, Zack Barresse, aka firefytr "FGOMEZ" wrote in message ... 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 |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to set header
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) "FGOMEZ" wrote in message ... 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 |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Macro to set header
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. :( -- Regards, Zack Barresse, aka firefytr "FGOMEZ" wrote in message ... 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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Building a Header Macro | Excel Discussion (Misc queries) | |||
Macro for Custom Header | Excel Discussion (Misc queries) | |||
Custom Header Macro | Excel Discussion (Misc queries) | |||
Header and Footer Macro | Excel Discussion (Misc queries) | |||
Macro with a different Header for each Sheet | Excel Discussion (Misc queries) |