Add filename to footer before printing using VBA
 

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.

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

--
Regards
Frank Kabel
Frankfurt, Germany

"PCE Man" <PCE schrieb im Newsbeitrag
...
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.

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 ToolsMacroMacros.

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
wrote:

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.

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


"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"PCE Man" <PCE schrieb im Newsbeitrag
...
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.

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

--
Regards
Frank Kabel
Frankfurt, Germany

"PCE Man" schrieb im Newsbeitrag
...
Thanks, that works.
What if I wanted to add text from a worksheet cell into a header?


"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"PCE Man" <PCE schrieb im
Newsbeitrag
...
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.

Great help.
Thanks Germany from the US

"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"PCE Man" schrieb im Newsbeitrag
...
Thanks, that works.
What if I wanted to add text from a worksheet cell into a header?


"Frank Kabel" wrote:

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

--
Regards
Frank Kabel
Frankfurt, Germany

"PCE Man" <PCE schrieb im
Newsbeitrag
...
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.