VBA – Three easy Excel Macros which will make your life easier

Posted on August 17, 2013 by

With this article I am going to present just a few easy Excel Macros, which I have found useful once. The first one is a typical example of loops in VBA and it creates an excel spreadsheet with tabs for each day. It is quite useful, if you are working in a company where at the beginning of the year you should create a template document for some kind of reporting. And it will save you about 50 minutes unpleasant work. So here is the macro:

Sub InsertTabs()

Dim i As Integer
Workbooks.Add
For i = 1 To 365
 Worksheets.Add Before:=Worksheets(Worksheets.Count)
 ActiveSheet.Name = "Day " & i
Next i
Worksheets(1).Activate

End Sub

Sub InsertTabs()

Dim i As Integer

Workbooks.Add

For i = 1 To 365

Worksheets.Add Before:=Worksheets(Worksheets.Count)

ActiveSheet.Name = "Day " & i

Next i

Worksheets(1).Activate

End Sub

If you want to change the number of added tabs, just change the value “365” in the macro. Two tricky parts exist in this code:

1. The usage of “Worksheets.Add Before:= Worksheets(Worksheets.Count)” This “Add Before” makes the order ascending.

2. The usage of Workbooks.Add, which creates a new file.

So, the second example of a loop in VBA Excel will put a date at the top of every spreadsheet in the file. The top is defined as the A1 cell and actually it is a really easy macro once you are aware of the functioning of the “For Each” loop. In short, this loop just makes an action for each variable in a set. Here is the code:

Sub PutDate()

Dim WS As Worksheet
For Each WS In ThisWorkbook.Worksheets
 WS.Range("A1").Value = Date
Next WS

End Sub

Sub PutDate()

Dim WS As Worksheet

For Each WS In ThisWorkbook.Worksheets

WS.Range("A1").Value = Date

Next WS

End Sub

In our case the variable is called WS (coming from worksheet, and yes it is a really bad name for a variable, but in a 6 line code I can afford bad names). This variable is defined as Worksheet and the loop actually looks at every worksheet at the workbook putting the date at the A1 cell. It is as easy as that.

The third example of easy VBA is probably the most useful of all the 3. Roughly, it makes an automatic print preview of the selected page, adding the following useful additional information:

1. The value of a given cell. (in the macro it is A1, but it can be changed)

2. The name of the company, to which the Office Package is registered. If everything is leagal, it should be your company.

3. The date

4. The name of the tab and the name of the file

5. The name of the author of the file (as per the registration of the Office Package)

6. Information about the number of pages in the document in format 7 out of 10 pages.

So, here is the magic code:

Sub FooterAndHeader()

With ActiveSheet.PageSetup
    .LeftHeader = ActiveSheet.Range("A1").Value
    .CenterHeader = ThisWorkbook.BuiltinDocumentProperties("Company")
    .RightHeader = Format(Date, "DD. MMMM YYYY")
    .LeftFooter = "&F/&A"
    .CenterFooter = ThisWorkbook.BuiltinDocumentProperties("Author")
    .RightFooter = "&P of &N Pages"
End With
    ActiveWindow.SelectedSheets.PrintPreview

End Sub

Sub FooterAndHeader()

With ActiveSheet.PageSetup

.LeftHeader = ActiveSheet.Range("A1").Value

.CenterHeader = ThisWorkbook.BuiltinDocumentProperties("Company")

.RightHeader = Format(Date, "DD. MMMM YYYY")

.LeftFooter = "&F/&A"

.CenterFooter = ThisWorkbook.BuiltinDocumentProperties("Author")

.RightFooter = "&P of &N Pages"

End With

ActiveWindow.SelectedSheets.PrintPreview

End Sub

If you have worked in a warehouse, where Excel is used 10 times more than the introduced ERP system, you will probably appreciate the three pieces of codes above!

Enjoy them and have a great day!