A macro is an excel feature used to automate tasks. As an accountant, you always carry out repetitive task each day and you can automate some of those tasks to make your work easier.
A macro is a set action recorded through the visual basic application to help in speeding up operations with an organization.
A macro code can save you a lot of time and help carry out a complex task. No more worries about the limited excel functions.
To run any macro code, ensure the Developer tab is enabled in your Excel workbook.
To get the most out of it, you need to know the different types of macros and what you can do with them.
This list consists of 10 useful macros that makes you more productive.
1. Save all
The save all macro is important to help you save all the open work works. Always make sure to run this macro before running other macros or task which may freeze your excel window. This helps you from losing your data. You can add a shortcut key to this macro to easily run it. Mostly named SaveAll
Copy the code below in
Sub SaveAll ()
Dim Wkb As Workbook
For Each Wkb In Workbooks
If Not Wkb.ReadOnly And Windows (Wkb.Name).Visible Then
2. Copy sum of selected cells
This macro is used when you need to copy sum of several excel cells scattered in different areas within your worksheet. Select the cells you need to sum and run the macro.
Add the macro and name it CopySelectedSumValue.
Copy the code below and paste it in the code window.
Sub CopySelectedSumValue ()
Dim MyDataObj As New DataObject
MyDataObj.SetText Application.Sum (Selection)
This macro enables you to open calculator directly to your excel sheet.
Create a micro and name it OpenCalculator.
Sub OpenCalculator ()
Application.ActivateMicrosoftApp Index: =0
4. AutoFit Column
This macro allows you to quickly auto-fit all the columns in your excel sheet
Sub AutoFitColumns ()
5. Insert Multiple columns
When you run Insert multiple columns macro, it opens an input box that prompt you to enter the number of columns you want to add to your excel sheet.
Sub InsertMultipleColumns ()
Dim I As Integer
Dim j As Integer
Or Error GoTo Last
I=InputBox (“Enter number of columns to insert”, “Insert Columns”)
For j=1 To i
Selection.Insert Shift: =x1ToRight, CopyOrigin: =x1FormatRightorAbove
Last: Exit Sub
6. Mail workbook
Most of the communication within an organization is through sending files through the email. Running an email macro will open Microsoft Outlook email for you to send excel files. Adjust the receivers email address and the subject from the code before sending the email.
Sub Mail_Workbook ()
Dim OutApp As Object
Dim OutMail As Object
Set OutApp=CreateObject (“Outlook.Application”)
Set OutMail=OutApp.CreateItem (0)
On Errror Resume Next
On Error GoTo 0
7. Attach workbook to email
The email macro enables you to attach workbook to your default mail client. Once you run the macro, it opens your email account and attaches the active workbook for you to easily forward it.
Sub OpenWorkbookAsAttachment ()
8. Goal seek
Goal seek can be used to solve complex problems in your workbook. Designing an automated macro code will enable you to easily search a specific input for the value added.
Sub GoalSeekVBA ()
Dim Target As Long
On Error GoTo Errorhandler
Target=Inputbox (“Enter the required value”, “Enter Value”) Worksheets (“Goal_seek”).Activate
ChangingCell: =Range (“C2”)
Errorhandler: MsgBox (“Sorry, value is not valid.”)
9. Create a backup
Backup macro is one of the most useful Excel tools for every accountant. It enables you to save a backup copy of the current workbook at the same directory with the saved file. You can also add date and name of the backfile to the macro created.
Sub FileBackUp ()
ThisWorkbook.SaveCopyAs Filename: =ThisWorkbook.Path &_”” & Format (Date, “mm-dd-yy”) & ” ” &_ThisWorkbook.Name
10. Highlight cells with comments
In accounting, a lot of comments and highlights are added to financial data. You can record a macro which selects all the cells with a comment.
Sub HighlightCellsWithCommnents ()
ActiveSheet.Cells.SpecialCells (x1CellTypeComments).Interior.Color =VbBlue
There are a variety of macro codes you can add to your excel sheet to be more productive. The above list consists of important macros you need to add to your workbook to automate your daily task and work smart.