Using a macro to add information to each sheet printed from the workbook in Microsoft

[wjgreen]

I found the macro below quite awhile ago on this site and have bee
using it ever since. It's great! However, it does not always
automatically place the information on every sheet of the workbook
(Sometimes) I have to manually go to each page and do a Print Preview
THEN the info appears on my printout and is updated automatically.

Am I missing a step??

Thanks.

++++++++++++++++++++++++++++++++++++++++++

Using a macro to add information to each sheet printed from th
workbook in Microsoft Excel

--------------------------------------------------------------------------------

Add a macro command that will automatically cause information
including the path, to appear on each sheet you print from th
workbook. When using this technique, the full path for where you save
the workbook will be printed in Excel 97 and 2000.

Open the VBE macro editor, and press Alt+F11.
In the Project Explorer, double-click the ThisWorkbook module.
Add an event called Workbook_BeforePrint.
In the event, type code as follows:

Private Sub Workbook_BeforePrint(Cancel as Boolean)
ActiveSheet.PageSetup.LeftFooter="&A&F&T&D" & ActiveWorkbook.Path
End Sub

The letters A, F, T, D = the name of the sheet, name of the workbook
time and date.


In Excel 2002, the middle line of code will be shorter:

.LeftFooter=="&A&F&T&D"&Z"


Save the file

 

[Frank Kabel]

Hi
try the following macro instead (changes all sheets)

Private Sub Workbook_BeforePrint(Cancel As Boolean)
Dim wkSht As Worksheet
For Each wkSht In Me.Worksheets
With wkSht.PageSetup
LeftFooter="&A&F&T&D" & ActiveWorkbook.Path
End With
Next wkSht
End Sub

 

[wjgreen]

I can't seem to get that to work at all. Do I have to run the macr
somehow before I print?

Let me be more specific as to what I'm trying to do and what th
problem is ...

Say I have a workbook with five worksheets.

Rather than select each one individually, I "Select all sheets" an
print them all at once.

If I rename the file, or copy / move it to a new directory, I want th
footer on each sheet to automatically change to reflect the ne
information.

With the macro I had been using, I have to at least go to EACH shee
and do a "Print preview" in order for the change to take effect. That'
not too onerous for five sheets, but with 35 sheets it's a pain!

For some reason, the new macro doesn't change anything, even when
print from the individual pages.

I was well versed in Lotus macros, but have limited (i.e., almost no
experience with Excel macros, so I'm doing it all by rote. An
additional help would be appreciated.

Thanks,

Bill Green
Miami, Florid

 

[Frank Kabel]

Hi
no you have to put this in your workbook module. Each time you print
8or go to print preview) it will be invoked automatically. To put it in
your workbook module try:
- open the VBA editor
- locate 'ThisWorkbook' in the left explorer tree view
- double click on this entry
- paste the code in the appearing editor window
- close the VBA editor, save the workbook and try it out

 

[wjgreen]

Just to close the loop ...

I found my original macro in a bulletin board, so I tried going back t
Mr. Excel for guidance. HIS original tip seems to work just fine. I
seems that the one on the bulletin board had some "minor" modification
that caused my glitch.

Here's the original (Mr Excel tip address embedded in the macro)"

Private Sub Workbook_BeforePrint(Cancel As Boolean)
' see http://www.mrexcel.com/tip055.shtml
For Each Sh In ActiveWorkbook.Worksheets
Sh.PageSetup.CenterFooter = ActiveWorkbook.FullName
Next Sh
End Su