How to set footer and header for all sheets?

[Kurt Remlin]

Hi,

I'm creating a brand new workbook with multiple sheets from VB code.
How do I programmatically assign Sheet Name as a header and 'Page 1 of
?' as a footer to all of them? Please advise.

TIA

 

[Trevor Shuttleworth]

Kurt

Select the first sheet by clicking on the tab; select the rest of the sheets
by shift clicking on the last tab.

Now select View | Header and Footer... | Header/Footer tab | modify the
header and footer as required.

If you add the tab name to the header and page information to the footer it
will be applied to all the selected sheets

Regards

Trevor

 

[Steve Garman]

Sub tester()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
With ws.PageSetup
.LeftHeader = "&A"
.LeftFooter = "Page &P of &N"
End With
Next ws
End Sub