PDA

View Full Version : Headlines



CptGreenwood
10-20-2005, 03:54 AM
Hi,

I want to have a headline (Date and Page#) on all printed excel pages BUT the first. I have found the option to create headers and footers but I didn't find the option to exclude the first page. Word can do that, I know... Excel can't?

Greetz,
Cpt

ALe
10-20-2005, 04:11 AM
Select all the sheets where you want the headlines (all sheets but the first).
To do it press Ctrl and click on the tabs of the sheets.

Then set the options for your headlines.

CptGreenwood
10-20-2005, 04:49 AM
Hi,

thanx for your answer but in my case it's not about sheets. My document has only a single sheet but printing it's contens requires more than one printed page.

Greetz,
Cpt

ALe
10-20-2005, 05:06 AM
Ok. As far as I know till now, I think you have to create a procedure that prints out the first page then set the headlines and finally prints out from 2nd to last page. I'll try to find a faster way to do it.

CptGreenwood
10-20-2005, 05:11 AM
Thanx. But, to complete the picture... the content size is variable so I cannot say at which row a new page begins. Must be found out by the automated pagebreaks.

ALe
10-20-2005, 05:20 AM
That's what the procedure must do:

1. Print the first page

ActiveWindow.SelectedSheets.PrintOut From:=1, To:=1, Copies:=1, Collate:=True

2. Set the headlines
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = "&F"
.RightHeader = "&D"
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.787401575)
.RightMargin = Application.InchesToPoints(0.787401575)
.TopMargin = Application.InchesToPoints(0.984251969)
.BottomMargin = Application.InchesToPoints(0.984251969)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SelectedSheets.PrintPreview

3. Print from page 2 to last page (in my case 5)

ActiveWindow.SelectedSheets.PrintOut From:=2, To:=5, Copies:=1, Collate:=True

4. delete the headlines

With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = ""
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.787401575)
.RightMargin = Application.InchesToPoints(0.787401575)
.TopMargin = Application.InchesToPoints(0.984251969)
.BottomMargin = Application.InchesToPoints(0.984251969)
.HeaderMargin = Application.InchesToPoints(0.5)
.FooterMargin = Application.InchesToPoints(0.5)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
End With
ActiveWindow.SelectedSheets.PrintPreview


Of course you can clear a lot of code. I put it to show the options in the macro recorder

CptGreenwood
10-20-2005, 05:23 AM
Sounds good. I'll try it. One thing: When printing the second to the last page I have to give the TO paramter. What to do if I don't know how many pages there are? Maybe it works when I leave TO blank, I'll see.

ALe
10-20-2005, 05:27 AM
Actually I don't know. Try it and we'll see. Let me know how it works. Sorry but I'm leaving now. Tomorrow I will be on-line again and I'll have a look to your thread.

mdmackillop
10-20-2005, 05:41 AM
For the "to" parameter, just give it a number far in excess of your number of pages. Excel will not print blank pages.
Regards
MD