HEADERS & FOTTERs

[rizaldo]

Hi

Basically I want to make a customize header / footer in excel 2007 . I can make it in one sheet but then when i go to the next sheet i have to do it again . Is there any way I dont have to repeat the process and header appears in every new sheet of the same workbook ?

Regards

Riz

 

[Domski]

You can select multiple sheets by holding down ctrl or shift and clicking on the tabs and then apply the page setup.

Dom

 

[Mark F]

If you want to copy ALL page formatting including Headers and Footers, try this

<TABLE style="WIDTH: 359pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=478 border=0 x:str><COLGROUP><COL style="WIDTH: 65pt; mso-width-source: userset; mso-width-alt: 3669" width=86><COL style="WIDTH: 42pt" span=7 width=56><TBODY><TR style="HEIGHT: 27pt; mso-height-source: userset" height=36><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 359pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 27pt; BACKGROUND-COLOR: transparent" width=478 colSpan=8 height=36>1. Activate the sheet that contains the desired setup info. This is the "source" sheet.</TD></TR><TR style="HEIGHT: 25.5pt; mso-height-source: userset" height=34><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 359pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 25.5pt; BACKGROUND-COLOR: transparent" width=478 colSpan=8 height=34>2. Select the "target" sheets. Press Ctrl and click the sheet tabs of the sheets you want to update with the settings from the source sheet.</TD></TR><TR style="HEIGHT: 18.75pt; mso-height-source: userset" height=25><TD class=xl33 style="BORDER-RIGHT: #ece9d8; BORDER-TOP: #ece9d8; BORDER-LEFT: #ece9d8; WIDTH: 359pt; BORDER-BOTTOM: #ece9d8; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent" width=478 colSpan=8 height=25>3. Select File, Page Setup and click OK.</TD></TR></TBODY></TABLE>

Mark

 

[Yard]

Quickest way is to multiselect the sheets you want (using Ctrl) and then apply the Headers and Footers. They will be applied to each sheet you selected.

Welcome, BTW!

 

[rizaldo]

Thank you for the replys .

umm how do i ' apply page setup ' ?

is there another way where when i click on the new sheet the page setup is as per the previous sheet ( instead of having to do it agian and again )

thanks

 

[vinod9111]

perhaps u can activate the sheet where you have done the page set up. go to file-------page set up---------click ok, then select the sheets where u want to keep the same page set up, click edit-------repeat page set up.

 

[Yard]

Try something like this in the ThisWorkbook module:

Private Sub Workbook_NewSheet(ByVal Sh As Object)
 
Dim wsMaster As Worksheet
 
Set wsMaster = ThisWorkbook.Worksheets("Master")
'This assumes you want to copy the page setup from a sheet called "Master"
'You can change this to suit
 
With Sh.PageSetup
        .LeftHeader = wsMaster.PageSetup.LeftHeader
        .CenterHeader = wsMaster.PageSetup.CenterHeader
        .RightHeader = wsMaster.PageSetup.RightHeader
        .LeftFooter = wsMaster.PageSetup.LeftFooter
        .CenterFooter = wsMaster.PageSetup.CenterFooter
        .RightFooter = wsMaster.PageSetup.RightFooter
        .LeftMargin = wsMaster.PageSetup.LeftMargin
        .RightMargin = wsMaster.PageSetup.RightMargin
        .TopMargin = wsMaster.PageSetup.TopMargin
        .BottomMargin = wsMaster.PageSetup.BottomMargin
        .HeaderMargin = wsMaster.PageSetup.HeaderMargin
        .FooterMargin = wsMaster.PageSetup.FooterMargin
        .PrintHeadings = wsMaster.PageSetup.PrintHeadings
        .PrintGridlines = wsMaster.PageSetup.PrintGridlines
        .PrintComments = wsMaster.PageSetup.PrintComments
        .CenterHorizontally = wsMaster.PageSetup.CenterHorizontally
        .CenterVertically = wsMaster.PageSetup.CenterVertically
       
        'OK, I got bored here, but you get the idea...set the rest of the values up and it should replicate whatever you have set up in the "Master" sheet
 
        .Orientation = xlPortrait
        .Draft = False
        .PaperSize = xlPaperA4
        .FirstPageNumber = xlAutomatic
        .Order = xlDownThenOver
        .BlackAndWhite = False
        .Zoom = False
        .FitToPagesWide = 1
        .FitToPagesTall = 1
        .PrintErrors = xlPrintErrorsDisplayed
        .OddAndEvenPagesHeaderFooter = False
        .DifferentFirstPageHeaderFooter = False
        .ScaleWithDocHeaderFooter = True
        .AlignMarginsHeaderFooter = True
        .EvenPage.LeftHeader.Text = ""
        .EvenPage.CenterHeader.Text = ""
        .EvenPage.RightHeader.Text = ""
        .EvenPage.LeftFooter.Text = ""
        .EvenPage.CenterFooter.Text = ""
        .EvenPage.RightFooter.Text = ""
        .FirstPage.LeftHeader.Text = ""
        .FirstPage.CenterHeader.Text = ""
        .FirstPage.RightHeader.Text = ""
        .FirstPage.LeftFooter.Text = ""
        .FirstPage.CenterFooter.Text = ""
        .FirstPage.RightFooter.Text = ""
End With

End Sub