Tip: Using PERSONAL.XLSB

If you want to save your macros to be able to use them in any Excel file you open, you will need to save them to your PERSONAL.XLSB file. This is a hidden file that is open and running in the background anytime you have an excel file open. So if you save a macro to this file you will always be able to run your macro.

  1. Open the Visual Basic Editor (Alt +F11)
  2. In the Project Explorer window on the left you will see a list of VBA projects. This is where you want to select the PERSONAL file instead of your current workbook. Click VBAProject(PERSONAL).
  3. Click Insert on the top tool bar and select module. You will see a new window popup in the Visual Basic Editor. This will be where you will paste the macros in future posts.
  4. After you paste the macro into the new module, click Save, and Close the Visual Basic Editor.
Advertisements

Macro: Save All

This macro is helpful for saving all open excel workbooks. This macro is helpful if you have several excel workbooks open and want to save them all at once. It is best to run this macro before running other macros or performing tasks that could potentially freeze up your excel to prevent losing your work.

Once you add this macro all you will need to do is run the macro (the macro will be named SaveAll).

Assign this macro to a shortcut key to gain the full time-saving benefit.

Excel Macro Code:

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

View the full listing of macros.

Macro: Comments and Highlights

This macro is a great review tool if you use a lot of comments and highlights while editing your worksheets.

Running this macro will generate a new tab at the front of your worksheet with a listing of every cell with a comment or highlight in the workbook. Each cell reference is a hyperlink that will lead you directly to the cell with a comment or highlight. The summary tab will list the value within the cell and the text in the comment.

Additionally there is an “Accept” button, which will remove the highlight or delete the comment from the applicable cell. Once you have accepted all changes the summary tab will be deleted.

Note: This macro is set up to find only certain highlights of yellow, but can be modified for you personal use. Please comment below and we will help you adjust the macro to your personal needs.

Click “continue reading” to see the macro code below. While it looks like a lot, all you need to do is copy and paste.

Once you add this macro all you will need to do is run the macro (the macro will be named comments_and_highlights).

Excel Macro Code:

Continue reading

Macro: Insert a Check Mark

This macro is helpful for footing (adding a column of numbers) trial balances, schedules, and reconciliations. It is common practice to put a check mark below the total to show that the column total is accurate. This macros will insert a red check mark in the active cell.

Once you add this macro all you will need to do is select the cell you want a check-mark in and run the macro (the macro will be named Checkmark).

Assign this macro to a shortcut key to gain the full time-saving benefit (recommended shortcut: ctrl+shift+v).

Excel Macro Code:

Sub Checkmark()
 ActiveCell.FormulaR1C1 = "P"
 ActiveCell.Select
 With Selection.Font
 .Name = "Wingdings 2"
 .Size = 11
 .Strikethrough = False
 .Superscript = False
 .Subscript = False
 .OutlineFont = False
 .Shadow = False
 .Underline = xlUnderlineStyleNone
 .ThemeColor = xlThemeColorLight1
 .TintAndShade = 0
 .ThemeFont = xlThemeFontNone
 End With
 Selection.Font.Bold = True
 With Selection.Font
 .Color = -16776961
 .TintAndShade = 0
 End With
 With Selection
 .HorizontalAlignment = xlCenter
 .VerticalAlignment = xlBottom
 .WrapText = False
 .Orientation = 0
 .AddIndent = False
 .IndentLevel = 0
 .ShrinkToFit = False
 .ReadingOrder = xlContext
 .MergeCells = False
 End With
End Sub

View the full listing of macros.

Macro: Mail Workbook

Today’s macro is helpful if you send a lot of excel files via email. Running the macro will create a new Microsoft Outlook email with the last saved version of the open workbook. By default the email will not be addressed to anyone, the subject of the new email will be the name of workbook, and the body will be “See attached.” These settings can be customized by editing the macro code below. It might be helpful to change the to: line to the email address of your supervisor or someone you send your spreadsheets to often.

Once you add this macro all you will need to do is run the macro (the macro will be named Mail_Workbook).

Excel Macro Code:

Sub Mail_Workbook()
 Dim OutApp As Object
 Dim OutMail As Object

 Set OutApp = CreateObject("Outlook.Application")
 Set OutMail = OutApp.CreateItem(0)

 On Error 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

View the full listing of VBA macros.

Macro: Copying the Sum of Selected Cells

I wanted to start off with a fairly simple excel macro first. Although simple, this macro is very helpful when you want to copy the sum of several cells in an excel spreadsheet. The cells can be scattered throughout the spreadsheet or all in the same row/column.

Once you add this macro all you will need to do is highlight the cells you want to sum, run the macro (the macro will be named CopyDelectedSumValue), and then paste into the cell you want the sum in.

Note: This will just paste the value of the sum, not a sum( ) formula.

Excel Macro Code:

Sub CopySelectedSumValue()
   Dim MyDataObj As New DataObject
   MyDataObj.SetText Application.Sum(Selection)
   MyDataObj.PutInClipboard
End Sub

View the full listing of macros.

Step Four: Assigning a Shortcut Key to a Macro

To save even more time you can assign a shortcut key for your macro. Follow the following steps to assign a shortcut key:

1. Click the Developer tab in the excel ribbon (if you do not see the Developer tab see the post on adding the developer tab).

2. Click the Macros icon (second on the left). If you have properly added a macro to your worksheet, you should see a list of your macros in this window.

Note: (Alt + F8) is a useful shortcut to bring up the Macros window.

3. Select the macro you would like to run and click Options.

4. Type your preferred shortcut key in the box under “Shortcut Key: “.  It must be a letter and you can also hold shift and your letter to increase your shortcut options.

Note: If you use a shortcut that is already used by excel it will override that shortcut. I plan to add a post in the future with all the excel shortcuts and shortcut keys that are unused by default.