This code may give you a start and it will probably need to be modified to
give you the results you want. Headers and footers are rather primitive in
Excel. This code will use the contents of cells A1, B1 and C1 to put text
into the left, center and right sections of the header for a sheet or sheets
that you specify, and will use the contents of cells A15, B15 and C15 to do
the same thing in the footer section for those sheets specified. It goes
into the Workbook code module.
To place it properly within the workbook: Right-click on the Excel icon
immediately to the left of the word File in the Excel menu bar and choose
[View Code] from the list that appears. Copy the code below and paste it
into the module in Excel. It will work each time you either print or
print-preview the sheets.
Private Sub Workbook_BeforePrint(Cancel As Boolean)
'set up headers/footers on a particular sheet or sheets
Dim WS As Worksheet
'this will allow you to set up several sheets
For Each WS In Worksheets
Select Case WS.Name
Case "Sheet1", "Sheet3"
SetHeaderAndFooter WS
End Select
Next
'the above can also be written as:
'For Each WS In Worksheets
' Select Case WS.Name
' Case "Sheet1"
' SetHeaderAndFooter WS
' Case "Sheet3"
' End Select
'Next
End Sub
Private Sub SetHeaderAndFooter(anySheet As Worksheet)
Application.ScreenUpdating = False
With anySheet.PageSetup
.LeftHeader = anySheet.Range("A1")
.CenterHeader = anySheet.Range("B1")
.RightHeader = anySheet.Range("C1")
.LeftFooter = anySheet.Range("A15")
.CenterFooter = anySheet.Range("B15")
.RightFooter = anySheet.Range("C15")
End With
Application.ScreenUpdating = True
End Sub
"mahbub" wrote:
> Hi JLatham
>
> Thanks for ur response.
>
> Do u know, have any procedure to link two row one for header and another for
> footer. I will be glad if u help my by suggestion.
>
> Thanks
>
> BR//
>
> Mahbub
>
> "JLatham" wrote:
>
> > You can have half of your wish:
> > Using File | Page Setup | [Sheet] tab, you can use "Rows to repeat at top"
> > to repeat row 1 on each page printed from that sheet, but you can't use rows
> > that are separated. So you could repeat rows 1 through 15, but not just rows
> > 1 and 15.
> >
> > You can, of course, use the same File | Page Setup | [Header/Footer] tab to
> > set up text that would be placed in the header/footer for each page printed
> > from that sheet.
> >
> > "mahbub" wrote:
> >
> > > Hi
> > >
> > > Suppose row 1 i want to use for header for everypage and row 15 which i want
> > > to setup as a footer. is it possible in excel.
> > >
> > > BR//
> > >
> > > Mahbub
|
Similar Threads
