adding footer to all existing sheets in workbook and also to newsheets which is added

[dinesh]

Urgnently i need a macro for adding footer to all existing sheets in
workbook and also to new sheets which is added

for example ther are two sheets named Yearly report, monthly report.
When I open this workbook i need to add a footer.also if i add a new
sheet called weekly report, then the footer has to come automatically.
Pls help me frends.Its urgent

 

[benmcclave]

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