A macro is an excel feature used to automate tasks. As an accountant, you always carry out repetitive tasks 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 make 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 tasks 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 the 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 into 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 the 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 prompts 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
6. Mail workbook
Most of the communication within an organization is through sending files through email. Running an email macro will open Microsoft Outlook email for you to send excel files. Adjust the receiver's 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 an email
The email macro enables you to attach a 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
The 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 in the same directory with the saved file. You can also add the 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 that 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.