Dinesh,

Try the following:

Step 1, record a macro where you create a footer with all settings you wish the footers to have.

Step 2, open the VBA editor and change the very first line from "Sub Macro1()" to "Sub CreateFooter(wsSheet as Worksheet)".

Step 3, do a Find and Replace to replace all instances of "ActiveSheet" with "wsSheet".

Step 4, go to the "ThisWorkbook" module and paste this code (this will add footer to any new sheet):

Private Sub Workbook_NewSheet(ByVal Sh As Object)
Call CreateFooter(Sh)
End Sub

Step 5 (optional), add this code to your VBA project and run it from an active workbook to apply footer to all sheets:

Sub AddFooter()
Dim ws As Worksheet
For Each ws In ActiveWorkbook.Worksheets
Call CreateFooter(ws)
Next
End Sub

Hope this helps,

Ben