• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Footer

V

vengal1987

New Member
Hi team,


I am trying to add footer in excel. And I want footer would be permanent.

I.e. When ever I open an excelsheet or workbook or insert a sheet that footer need to be there.


So,could you please help me how to do this.


Its ok if its done this by macro or is there any setting need change in excel
 
Goto File, Print, Page Setup

Header/Footer tab


You have to setup Footers manually for each worksheet

There isn't an option for establishing a default Footer


It could be done via VBA if required
 
Good day vengal1987


If you do as Hui has suggested and set up your Headers and Footers in page setup and you are happy with your results (after playing around with the settings),you could then start the macro recorder and the do your final setps , the one you are happy with , this with give you a macro you could use in all your work books and by looking at the macro you will see how VBA handles it.
 
Thanks for reply. But I know that manual one.

And I did tried macro for footer,but what happening is for every new workbook or new sheet I have run macro.

I.e I have to open that macro and need to run it whenever I open new workbook or sheet.


Further, to inform you, I am working on my client location virtual desktop and in that region there is default footer there, means wh whenever I open open new workbook or new sheet that footer is there by default. Now, I dnt knw how.it.created.

So, please recheck and advise.
 
Hi ,


You can create what are called templates ; check these links :


1. http://office.microsoft.com/en-in/excel-help/about-excel-templates-HP005229286.aspx


2. http://office.microsoft.com/en-in/excel-help/save-a-workbook-or-worksheet-as-a-template-HA010218874.aspx


3. http://www.techrepublic.com/blog/msoffice/modify-excels-default-sheet-to-fit-your-needs/2672


Narayan
 
Good day vengal1987


If you read these links and then create your macro you will have it in all workbooks


http://www.ozgrid.com/VBA/excel-add-in-create.htm


http://office.microsoft.com/en-us/excel-help/deploy-your-excel-macros-from-a-central-file-HA001087296.aspx
 
Hi Bobhc,


I have tried with above links, but I am unable to sucess with these.


So,I have created a small macro to add footer, but the macro is giving the footer in a particular sheet only.( and i have to run that macro in every sheet)


Here,I want that macro will give the Footer to a complete worksheet at one shot.


Below is VBA Code:- (and please if any mistake in my code)


Sub macro()

'Footer

Dim ws As Worksheet

For Each ws In ActiveWorkbook.Worksheets


With ws.PageSetup


'.LeftFooter

.CenterFooter = "Limited access only"

'.RightFooter


End With

Next ws

End Sub
 
Good day vengal1987


One foot out the door on my way for dinner so did a quick macro builder to put a footer name in work sheet a this is the code some of which is not needed but quite a bit longer than code posted by your self!! footer called coos.

[pre]
Code: 
Sub footer()
'
' footer Macro
'

'
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.PrintTitleRows = ""
.PrintTitleColumns = ""
End With
Application.PrintCommunication = True
ActiveSheet.PageSetup.PrintArea = ""
Application.PrintCommunication = False
With ActiveSheet.PageSetup
.LeftHeader = ""
.CenterHeader = ""
.RightHeader = ""
.LeftFooter = ""
.CenterFooter = "coose"
.RightFooter = ""
.LeftMargin = Application.InchesToPoints(0.708661417322835)
.RightMargin = Application.InchesToPoints(0.708661417322835)
.TopMargin = Application.InchesToPoints(0.748031496062992)
.BottomMargin = Application.InchesToPoints(0.748031496062992)
.HeaderMargin = Application.InchesToPoints(0.31496062992126)
.FooterMargin = Application.InchesToPoints(0.31496062992126)
.PrintHeadings = False
.PrintGridlines = False
.PrintComments = xlPrintNoComments
.PrintQuality = 600
.CenterHorizontally = False
.CenterVertically = False
.Orientation = xlPortrait
.Draft = False
.PaperSize = xlPaperA4
.FirstPageNumber = xlAutomatic
.Order = xlDownThenOver
.BlackAndWhite = False
.Zoom = 100
.PrintErrors = xlPrintErrorsDisplayed
.OddAndEvenPagesHeaderFooter = False
.DifferentFirstPageHeaderFooter = False
.ScaleWithDocHeaderFooter = True
.AlignMarginsHeaderFooter = True
.EvenPage.LeftHeader.Text = ""
.EvenPage.CenterHeader.Text = ""
.EvenPage.RightHeader.Text = ""
.EvenPage.LeftFooter.Text = ""
.EvenPage.CenterFooter.Text = ""
.EvenPage.RightFooter.Text = ""
.FirstPage.LeftHeader.Text = ""
.FirstPage.CenterHeader.Text = ""
.FirstPage.RightHeader.Text = ""
.FirstPage.LeftFooter.Text = ""
.FirstPage.CenterFooter.Text = ""
.FirstPage.RightFooter.Text = ""
End With
Application.PrintCommunication = True
End Sub
[/pre]
As I said a lot of code not needed but a lot is.
 
bobhc Thanks for reply.


But, This macro is also not serving the purpose, as this macro also giving footer on 1st sheet itself(which is the issue itself, as I want these macro should give footer for entire workbook)


Kindly advise
 
Did you tried to write an add-in with application event.

http://www.mediafire.com/download/3ulwr961u8pjzxu/events.xlam

you need to implement AddFooter(...)

and install this add-in in order to work for any new|opened workbook or sheet added

[pre]
Code: 
Private WithEvents App As Application
Private Sub Workbook_Open()
Set App = Application
End Sub
Private Sub App_NewWorkbook(ByVal Wb As Workbook)
MsgBox "a new workbook was added!"
'AddFooter(sh) for sh in Wb
End Sub
Private Sub App_WorkbookNewSheet(ByVal Wb As Workbook, ByVal Sh As Object)
MsgBox "a new sheet was added!"
'AddFooter(sh)
End Sub
Private Sub App_WorkbookOpen(ByVal Wb As Workbook)
MsgBox "a workbook was opened!"
'AddFooter(sh) for sh in Wb if Checkfooter(sh)
End Sub
[/pre]
 
I don't know how to redit my post but try this version...

[pre]
Code: 
Option Explicit
Private WithEvents App As Application
Private Sub Workbook_Open()
Set App = Application
End Sub
Private Sub App_NewWorkbook(ByVal wb As Workbook)
AddFooters wb
End Sub
Private Sub App_WorkbookNewSheet(ByVal wb As Workbook, ByVal sh As Object)
AddFooter sh
End Sub
Private Sub App_WorkbookOpen(ByVal wb As Workbook)
AddFooters wb
End Sub
Sub AddFooter(sh As Worksheet)
With sh.PageSetup
.CenterFooter = "Limited access only"
End With
End Sub
Sub AddFooters(wb As Workbook)
Dim ws As Worksheet
For Each ws In wb.Worksheets
AddFooter ws
Next ws
End Sub
[/pre]
I hope this is what you ask
 
Good day vengal1987....


Add the footer to the Default Workbook and the Default Sheet.

Try this out:


Select all the sheets in a blank workbook and then add the footer.

Ungroup the sheets and save the file with the name Book.

Save it as an XLTX file.

Save it in the XLStart folder. - do a Windows search for that, because it can be anywhere depending on the setup.


To create a default Sheet, follow the same procedure as above but first remove all but one of the sheets from a blank workbook and then customize that sheet. Save this file with the name Sheet, make it a template (*.xltx) file and save it to the same location as above (XLStart).


All the best.
 
+1 @James Perry, Cool... very good solution, ...(but it did not add a footer to an old Workbook).

Thanks for the tips.
 
You must log in or register to reply here.
Back
Top