Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
PCE Man
 
Posts: n/a
Default 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.
  #2   Report Post  
Frank Kabel
 
Posts: n/a
Default

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.


  #3   Report Post  
Gord Dibben
 
Posts: n/a
Default

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.


  #4   Report Post  
PCE Man
 
Posts: n/a
Default

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.



  #5   Report Post  
Frank Kabel
 
Posts: n/a
Default

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.






  #6   Report Post  
PCE Man
 
Posts: n/a
Default

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.




Reply
Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Printing 3Dchart with pics& texture Jeff K. Charts and Charting in Excel 0 January 6th 05 08:38 PM
Printing multiple columns on one page tdj Excel Discussion (Misc queries) 4 December 2nd 04 03:55 AM
Enable Double sided printing contiuously when printing multiple s. Lee Excel Discussion (Misc queries) 1 November 27th 04 02:58 AM
Printing too much! Tavish Muldoon Excel Discussion (Misc queries) 2 November 26th 04 11:46 PM


All times are GMT +1. The time now is 08:48 AM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"