Automating Footers

  • Thread starter Thread starter Mimi
  • Start date Start date
M

Mimi

Is there any way, in Excel 2002, to carry out the
following:

I have approximately 15 worksheets, the first of which is
a form type sheet, which is completed.

The subsequent pages are called App 2, App 3, App 4, App
5 to App5.10, App 6 & App 7, etc.

But, they could also be called CRM2, CRM3, CRM4 etc.

Is there any way in which I can specify the prefix of App
or CRM on the first page, and each footer is
automatically updated.

The reason for doing this is that some pages are
printed 'fit to one page', some are not, and the footer
font always remains the same. And, it is aligned with my
company logo in the centre footer.

I think this is impossible, without having two workbooks,
one for Apps and one for CRMs, but worth asking!
Many thanks
 
One way:

Put this in the ThisWorkbook code module (right-click the workbook title
bar and choose View Code):

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wsSheet As Worksheet
For Each wsSheet In ActiveWindow.SelectedSheets
With wsSheet
If UCase(.Name) Like "APP*" Then
.PageSetup.LeftFooter = "footer for Apps"
ElseIf UCase(.Name) Like "CRM*" Then
.PageSetup.LeftFooter = "footer for CRM"
Else
.PageSetup.LeftFooter = "something else"
End If
End With
Next wsSheet
End Sub

This will set the footer according to the sheet name. Adjust your
footers as desired. See VBA Help for header and footer codes if you want
to change fonts, etc., e.g.:

.PageSetup.LeftFooter = "&""Arial""&14something else"

You can substitute .CenterFooter or .RightFooter for .LeftFooter
 
Back
Top