Category: Excel

1. Record Macros

Recording macros can save huge amounts of time by automating a procedure or series of operations that you would otherwise have to do manually.

1. First add the “Developer” tab by following these steps:
   a. File > Options > Customize the Ribbon
   b. Check the “Developer” tab.

1. Recording a basic macro. Recording macros and watching the code appear is also a fantastic way to learn Visual Basic.
   a. Developer tab > Click “Record Macro”
   b. Perform a set of operations
   c. Developer tab > Click “Stop Recording”

Example: Formatting Financial Data.

Suppose I want to format a worksheet of financial data that I’ve downloaded, but I need to do the same thing to five other worksheets. Let’s make use of the developer tab by:

1. Recording the macro
2. Perform a set of operations
a. Delete columns G-M
b. Make the column headers bold.
c. Apply cell borders.
d. Apply financial data type to columns B-E.
e. Apply conditional formatting to column one based on the daily trend.
f. Center text.
3. Stop recording the macro.

Raw Data:

Processed Data:

Now we open the next worksheet we want to process Developer > Macros > “FormatFinancialData” > Click Run. And that’s that.

But what if I have to do this on a ton of worksheets?

Developer > Visual Basic > Modules > “Module1”

Below your recorded macro, paste the following lines of code:

Sub RunOnAllOpenSheets()
Dim WB As Workbook
Dim WS As Worksheet

For Each WB In Workbooks
    For Each WS In WB.Worksheets
        If WB.Name <> ThisWorkbook.Name Then
        WS.Activate
        Call FormatFinancialData
        End If
    Next WS
Next WB

End Sub

This will format the data on all worksheets of all open workbooks except the one where the macro is stored.

Calling Additional Macros:

You can call additional Macros by simply calling out their name in the RunOnAllOpenSheets macro:

Sub RunOnAllOpenSheets()
Dim WB As Workbook
Dim WS As Worksheet

For Each WB In Workbooks
    For Each WS In WB.Worksheets
        If WB.Name <> ThisWorkbook.Name Then
        WS.Activate
        Call FormatFinancialData
        Call AdditionalMacro1
        Call AdditionalMacro2
        End If
    Next WS
Next WB

End Sub

2. Perform Tasks Automatically

Run a Macro Each Time the Workbook is Opened:

1. Open a workbook. Go to the Developer tab.
2. Click on Visual Basic. Double-click on “ThisWorkbook” in the Project Explorer on the left-hand side.
3. In the first dropdown menu, select “Workbook”, on the second dropdown menu, select “Open”.
4. Add the code that you want to execute or call one (or more) of your existing macros as shown below:

Run a Macro When a Cell Changes:

1. Open a workbook. Go to the Developer tab.
2. Click on Visual Basic. Double-click on “Sheet1 (Sheet1)” in the Project Explorer on the left-hand side.
3. In the first dropdown menu, select “Worksheet”. In the second dropdown menu, select “Change”.
4. Add an If-statement that specifies the cell (or range) that when changed, will trigger your macro(s).  In this case, we specify the cell “A2”.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A2") Then
Call Macro1
End If
End Sub

Run a Macro at a Scheduled Time (Part 1):

1. Open a workbook. Go to the Developer tab.
2. Click on Visual Basic. Double-click on “ThisWorkbook” in the Project Explorer on the left-hand side.
3. In the first dropdown menu, select “Workbook”. In the second dropdown menu, select “Open”.
4. Add the following line of code to specify the name of the macro and the time you would like it to run.

Private Sub Worksheet_Change(ByVal Target As Range)
If Target = Range("A2") Then
Call Macro1
End If
End Sub

1. Confirm that the macro you want to run is contained in a module in an open workbook.
2. Save the file as a macro-enabled workbook and re-open it.
3. In order for the macro to run at the given time, the workbook must be open.

But what if I don’t want to have the workbook open?

Run a Macro at a Scheduled Time (Part 2):

1. Write your Macro (and macro calls) in the “Workbook – Open” window in VBA.
2. Save your file.
3. In the Windows start menu, search for “Task Scheduler” and open the application.
4. Action > Create Task
   a. General > Name Your Task
   b. Triggers > New > Begin the task: “On a Schedule”
   i. Schedule your task appropriately
   c. Actions > New > Browse
   i. Select your .xlsm file

3. Automatically Send Reports

Note: This will only work with Outlook

1. Developer > VBA > Insert Module
   a. Paste the following code into your module:

Sub SendThisSpreadsheet()
    Dim Outlook As Object
    Dim Email As Object

    Set Outlook = CreateObject("Outlook.Application")
    Set Email = Outlook.CreateItem(0)
    With Email
        .to = "test@mailinator.com"
        .subject = "This is a Test"
        .body = "Hello," & vbNewLine & vbNewLine & _
              "Here is an updated version of MyWorkbook." & _
              vbNewLine & vbNewLine & "Thank you"
        .Attachments.Add _
         ("C:\Users\User\Documents\Spreadsheets\MyWorkbook.xls")
        .Display
        .Send
    End With
End Sub

b. This code sends an updated version of your spreadsheet out to the email addresses specified in the “.to” line of the code. Multiple addresses should be separated by commas inside the quotation marks.
c. The body of the email can be updated by changing the “.body” line of the code.
d. Replace “C:\Users\User\Documents\Spreadsheets\MyWorkbook.xls” with the path of the file you would like to send.
2. This code can be combined with the techniques described in the previous section to completely automate your email update process.

Sources

Automatic Emails with Outlook

Calloway EOD Prices – Quandl

Macro Task Scheduler

Mailinator

Run Macro at Schedule Time (Part 1)

Run Macro When Cell Changes

Run Macro When Workbook Opens

Run On All Open Workbooks

Send Emails Automatically

VBA Function Library