Add filename to footer before printing using VBA

[PCE Man]

Using VBA, how can I add the filename to the left foote, before printing, in
all worksheets of the open workbook, without manually opening up each
worksheet and adding a custom footer.

 

[Frank Kabel]

try putting the following type of code in your workbook
module 'ThisWorkbook' (don't put it in a standard module):
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In Me.Worksheets
With wkSht.PageSetup
.CenterFooter = me.name
End With
Next wkSht
End Sub

 

[Gord Dibben]

Sub Path_All_Sheets()
Dim ws as WorkSheet
Set wkbktodo = ActiveWorkbook
For Each ws In wkbktodo.Worksheets
ws.PageSetup.LeftFooter = ActiveWorkbook.FullName
Next
End Sub

Copy/paste to a general module and run using a button, shortcut key combo or
just from Tools>Macro>Macros.

Alternative......copy/paste to the ThisWorkbook module

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim ws as WorkSheet
Set wkbktodo = ActiveWorkbook
For Each ws In wkbktodo.Worksheets
ws.PageSetup.LeftFooter = ActiveWorkbook.FullName
Next
End Sub

Gord Dibben Excel MVP

On Thu, 2 Dec 2004 14:21:03 -0800, "PCE Man" <PCE

 

[PCE Man]

Thanks, that works.
What if I wanted to add text from a worksheet cell into a header?

 

[Frank Kabel]

Hi
e.g. for cell A1
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In Me.Worksheets
With wkSht.PageSetup
.CenterFooter = wksht.range("A1").value
End With
Next wkSht
End Sub

 

[PCE Man]

Great help.
Thanks Germany from the US

Hi
e.g. for cell A1
Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In Me.Worksheets
With wkSht.PageSetup
.CenterFooter = wksht.range("A1").value
End With
Next wkSht
End Sub