Useful Excel Macros for Accountants

Useful Excel Macros for Accountants

  • Post author:
  • Post category:Macros

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 ()

Application.ScreenUpdating =False

Dim Wkb As Workbook

For Each Wkb In Workbooks

    If Not Wkb.ReadOnly And Windows (Wkb.Name).Visible Then

    Wkb.save

    End if

Next

Application.ScreenUpdating=True

End Sub

 

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)

MyDataObj.PutInClipboard

End sub

 

3. Calculator

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

End Sub

 

4. AutoFit Column

This macro allows you to quickly auto-fit all the columns in your excel sheet

Sub AutoFitColumns ()

Cells.Select

Cells.EntireColumn.Autofit

End sub

 

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

ActiveCell.EntireColumn.Select

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

Next j

Last: Exit Sub

End Sub

 

6Mail 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

With OutMail

.To=””

.CC=””

.BCC=””

.Subject=ActiveWorkbook.Name

.body=”See attached.”

.Attachments.Add ActiveWorkbook.FullName

.Display

End With

On Error GoTo 0

Set OutMail=Nothing

Set OutApp=Nothing

End Sub

 

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 ()

Application.Dialogs (x1DialogSendMail).Show

End Sub

 

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

With Activesheet.Range(“C7”)

.GoalSeek_Goal:=Target, _

ChangingCell: =Range (“C2”)

End With

Exit sub

Errorhandler: MsgBox (“Sorry, value is not valid.”)

End Sub

 

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

End sub

 

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

End Sub

 

Conclusion

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.