Learn Excel Macros by Trying Sample Code

Now that you have looked at our other Excel Macros (you can view the first first Excel Macro here and Second Excel Macro here), its time to give you something you can explore your self.  This macros is sure to save you loads of time.  I have often been tasked with adding headers and footers to Excel Workbooks and if you have done the same you know how time consuming this can be.

In this example we will use a macro to insert a header and footer on every worksheet in the active workbook. This macro will also insert the complete path to the workbook. You will need to change the Left Footer to your own company name. This code is very much open for you to play around and experiment with.

I am not going to tell you how to add this code, because in the previous articles we have looked at adding the code to a worksheet and to the workbook. What I suggest is that you read the code and decide, is it a worksheet or workbook code? and they try it out. You will soon see if you are right or wrong.

Once you have copied and pasted the code correctly and see what has printed on the header and footers, you can then work through the code to amend what is currently showing.

Sub InsertHeaderFooter()

‘ inserts the same header/footer in all worksheets

Dim ws As Worksheet

Application.ScreenUpdating = False

For Each ws In ActiveWorkbook.Worksheets

Application.StatusBar = “Changing header/footer in ” & ws.Name

With ws.PageSetup

.LeftHeader = “with Thanks to www.theexcelclub.com”

.CenterHeader = “Page &P of &N”

.RightHeader = “Printed &D &T”

.LeftFooter = “Path : ” & ActiveWorkbook.Path

.CenterFooter = “Workbook name &F”

.RightFooter = “Sheet name &A”

End With

Next ws

Set ws = Nothing

Application.StatusBar = False

End Sub