Add a standard footer to an existing worksheet

[ExcelJAM]

My company has a lengthy confidentiality footer that must be added on every worksheet of every workbook. I often receive existing worksheets where I need to add this footer. Is there a way to quickly/automatically add it without affecting the other existing page set up features (e.g. page orientation, margins, etc.)?

I've searched the forum and found something similar that was answered with a Before_Print Event - however I need to ensure this is on all worksheets, even if they are never printed.

The footer is: Confidential Use Only. Disclose and distribute only to XX employees having a legitimate business need to know. Disclosure outside of XX is prohibited without authorization.

I would like it centered in an 8 pt font with a hard return after each sentence end.

I currently waste a TON of time dealing with this and would greatly appreciate any help anyone can offer.

 

[Gerald Higgins]

Hi ExcelJam, welcome to the board.

Have you considered adding it using the standard Page Setup, Header/Footer options ? This won't affect page orientation. It won't directly affect margins, although it will force space to be left for the footers, and from memory, I think this might alter how much you can print on each page, although you can probably correct for this by altering the scaling factor on the page tab.

 

[ExcelJAM]

Thanks - I'd like to avoid having to retype or copy the footer into each individual worksheet - I have had to do this to, literally, a hundred worksheets in a day!. When I select multiple worksheets and use the Header/Footer, the orientation of the first sheet selected usually gets applied to all worksheets. I was told this should work rather than accessing through Page SetUp, but it's only worked for me sporadically.

I tried recording a macro to add the footer, but when it runs, it also changes the orientation and margins to that of my Personal.xls where the macro was created.

Hi ExcelJam, welcome to the board.

Have you considered adding it using the standard Page Setup, Header/Footer options ? This won't affect page orientation. It won't directly affect margins, although it will force space to be left for the footers, and from memory, I think this might alter how much you can print on each page, although you can probably correct for this by altering the scaling factor on the page tab.

 

[lenze]

Hi and welcome to the board!!!

Sub AddFooter()
ActiveSheet.PageSetUp.CenterFooter = "xxxxx" & Chr(10) & "yyyyy" & Chr(10) & "zzzzz"
End Sub

xxxx yyyy zzzzz represent text
The Chr(10) is a Carriage Return

HTH
lenze

BTW, Place this in your Personal.xls macro workbook so it will always be available

 

[ExcelJAM]

Thanks lenze. You are actually the user who supplied the Before_Print Event answer that I thought was close. Forgive me, I'm new to macros & VB. Is this a macro I will have to run, or will it just automatically be applied to each worksheet?

Thanks for the help.
JAM

Hi and welcome to the board!!!

Sub AddFooter()
ActiveSheet.PageSetUp.CenterFooter = "xxxxx" & Chr(10) & "yyyyy" & Chr(10) & "zzzzz"
End Sub

xxxx yyyy zzzzz represent text
The Chr(10) is a Carriage Return

HTH
lenze

 

[lenze]

See my last edit above. Place this in your personal.xls so it's always available. Yes, you will have to manually run it. If you really want to automate it, it could be done using a WorkBook Sheet_Activate Event. It would, however, run anytime a sheet is activated. It will just overwrite the previous footer. Post back if that is of interest. I'm off line till tommorow. Good Luck

lenze

 

[ExcelJAM]

lenze -

I think the WorkBook Sheet_Activate Event would get me into trouble on other sheets, so just the macro will be fine. One more question, can I set the font to be 8 pt? It can be tought to work around a footer that large. Or better yet, is there a way to set your header and footer default font to something different than your worksheet default?

Thanks a million, my whole department will be thrilled to use this!

JAM

See my last edit above. Place this in your personal.xls so it's always available. Yes, you will have to manually run it. If you really want to automate it, it could be done using a WorkBook Sheet_Activate Event. It would, however, run anytime a sheet is activated. It will just overwrite the previous footer. Post back if that is of interest. I'm off line till tommorow. Good Luck

lenze

 

[lenze]

Regarding the font size. Chip Pearson explains it better than I can here
http://www.cpearson.com/excel/headfoot.htm Also, a Google search shoud lead you to other examples.

Where you able to place your code in the Personal.xls? That really is important. If not, post back and I will give you instructions.

lenze

 

[ExcelJAM]

lenze -

I did get it into my Personal.xls, but had some difficulty. Originally I right clicked on the excel icon to the left of "File" (as I had read your instruction in another post) and it saving as ThisWorkbook.AddFooter and would only run in my Personal.xls file.

I found where my previous attempt with a recorded macro was saved and copied and pasted it there. So it now works, but I'm wondering what steps I should've folllowed to put it out there correctly from the start.

Finding that the answer to such an aggravating problem was such a simple macro has inspired me to delve into the topic. I've put a hold on the Jelen book at my library - can you suggest any other good resources for a beginner to learn macros?

Thanks so much for your help,
JAM

Where you able to place your code in the Personal.xls? That really is important. If not, post back and I will give you instructions.

lenze[/quote]

 

[lenze]

Your mistake was in trying to place it in the ThisWorkBook module of you Personal.xls. This is a pure macro, not an Event procedure. The ThisWorkBook module (like the Sheet modules) is only used for Event procedures. You need to open the VBE and insert a module. That's where the code goes. When you used the macro recorder, that's what it did. Does it now run on all Open WorkBooks??

lenze

No help on book suggestions. I've never read one. Most all I know I learned from the board