VBA Training Series – How to Write Your First Macro (Part 1 of 3)

Macros & VBA Training Series Part 1:

Writing Your First Macro & The Excel Object Model

Double-click video to view in Full Screen HD.

Download the file used in this video:

My First Macro.xslm (93.0 KB)

Macros & VBA Training Series
Enroll for FREE

The video above is the first in a 3-part training series. Can I send you the next two videos?

Please click the link below to enroll in the free training. I will send you video #2 in just a few days.​

Video 1

Intro to VBA:
Writing Your First Macro

Video 2

Automating Tasks:
The Summary Report

Video 3

Userforms:
The Exports Sheets App

Please click the big green button above to register for the free training series.

About Me

Welcome to Excel Campus!  I am excited you are here!

My name is Jon Acampora and my goal is to help you learn Excel to save time with your job and advance in your career.  I've been an avid Excel user and VBA developer for 10+ years.  I am also a Microsoft MVP.  When I'm not looking at spreadsheets, I get outdoors and surf. 🙂

What Process Do You Want To Automate in Excel?

Please leave a comment below with your answer, and any questions.  Thanks!​

1,559 comments

Your email address will not be published. Required fields are marked *

  • I found video very useful, i have been wanting to learn macros for so long now and this video helped me to understand the basic rules.
    I want to be able to pull data from several spreadsheets and put the data into one spreadsheet as a table format and chart.

  • I’m using the newest version of XL which I assume is 2019. My insert does not yield a module choice. I do not see the button you referred to where I can add a macro module VBA module. In 3rd the keyboard shortcut of alternate I with the letter and asked me to continue the code from an earlier version of XL.

  • I would like to automate my KPI’s, or Key Performance Indicators. I receive data several times a week from my company that spans 20-30 data properties albeit in an already clean Excel format. I want to be able to filter this data automatically and update a homemade KPI Data Table with ease. Sort of like feeding my company’s data into my table and my table pulling the data I need it to accurately every time.

  • How do I in Excel or with VBA take data from excel and put it into Word?
    Say I have to send a Weekly SITREP of my progress that is tracked in Excel and then write a Word document explaining the details…. I’d love to be able to have a Word template that uses the Excel data (tables, cells, etc) automatically instead of Copy/Paste into Word…. and then send the Word doc via email to the boss. =)

  • I did step 2 the same way and 2nd line up nothing? Should I be replacing something on the 2nd one?
    ——————————-
    Sub Macro1()


    Macro1 Macro

    ‘ Run Opitimzer

    X = SolverSolve(True)
    End Sub

    ———————————————————————
    Sub Macro4()

    Macro4 Macro

    ‘ Clear Optimizer

    Range(“I2”).Select
    ActiveCell.FormulaR1C1 = “0”
    Selection.AutoFill Destination:=Range(“I2:I201”)
    Range(“I2:I201”).Select
    End Sub
    —————————————————————————————————————————
    Sub Macro5()

    Macro5 Macro

    ‘ Mult2

    X = SolverSolve(True)
    End Sub

    ——————————————–
    Sub Macro6()

    Macro6 Macro

    ‘ Clear Optimizers

    Range(“I2”).Select
    ActiveCell.FormulaR1C1 = “0”
    Selection.AutoFill Destination:=Range(“I2:I201”)
    Range(“I2:I201”).Select
    End Sub

  • Very informativeand comprehensive video. I have never watched a video that clearly explaining scary codes. Thanks to this I am now looking forward on how to to automate things.

    Even if I am a beginner, I now have tasks I want to automate. Some of these are: records reconciliation, autocompilation of data based on the same form of input, and of course autogeneration of data from a set of information. These may sound complicated to me now and all but will try my best to get there even if I am not really a coding-type of person. In all honesty, I really am amassing so much courage to learn MACRO and rrlearn VBA becausr I hated it before. I really hope I find all guidance I can find through excel campus.

  • Thank you for your videos, it has helped me to relearn some basic functions of excel and automate some tasks.

    What I am struggling to automate in excel is the following.

    The excel document has 29 columns with specific formulas in 10 of the cells.
    There are also subtotal rows that tally up specific vendors totals before moving onto the next vendor and their inputs.
    There is a grand total row as well.

    What I am trying to do is create a macro that will enable a user to create a new vendor line above the grand total row. This new vendor line will need to have 10 rows + 1 subtotal row with formulas that will update and reference the correct new cells. The Grand total row will need to sum the newly added Subtotal row with the previous subtotals rows.

    The second part is that the subtotal row needs to then create a new row in sheet 2 (Profits & Loss) with the data from the subtotal row.

    How would I go about creating this automating this in excel?

    Thank you.

  • I have many objects that I wish to automate both in excel and word.

    The excel objects are based on a large dataset and my need is to set up a dashboard to allow management to see problems areas within a particular work process and pinpoint exactly where the problems are arising.

    In word we have many set templates which should follow a standardised formatting directive, but the staff are constantly changing and the directive is not being followed causing additional work for the support team and delaying of the required outputs to senior management. I would like to use VBA to setup automatic formatting and data entry to speed up the process.

    I have used VBA before, but a long time ago and so I need a refresher and learn about the new features that the MS applications have created to assist me in my quest to automate and streamline these processes.

  • Jon,
    Very good overview, thank you for that. I have a question, using VBA how would one horizontally center a specific pivot row field without using the pivot select method? Is there a way?

    Thanks so much.

  • My compliments the way u explain process to automate this is the basic but i am looking for most complex which might take months or ~~~~?
    any ready template ??

    1) I want to automate PnL for options stocks & commodity markets buy/sell I trade only Options calls. puts, spreads, ratio spread,strangle
    want to incorporate data from broker trading platform to independent xl. sheet as & when trades are complet which could be days,weeks, or months, Also like to create the graph for visual presentation, sure can be done !!

    2) complete spreadsheet to run gas station + convenience store business, lottery,money order, beer,wine
    Any ready templates free or reasonable price to put in immediate action .
    kanu

  • I’m not sure what processes I’d like to automate yet. I’m trying to get a refresher on working with VBA. Your videos and presentation style are excellent — very clear and straightforward.

  • Hi, this one’s a really good lesson to learn VBA as a beginner.
    I want the data from another excel where the data is updated on a daily basis, what i the code I should be using in order to get this thing done?

  • Hi Jon,

    I have a big spreadsheet with tons columns. My team of 10 are using it everyday. Each members has different interest in different fields (columns) so we often have to filter the table by some categories and hidden some columns.

    1. I want to create a button to clear the filter. I follow the exact steps in your video but the marco still not running. Dont’s know how to troubleshoot it.
    2. I also wonder if we can display all and sort the table by the “start date” column.
    3. I also want to create a marco that can do hiden and unhiden column and filter some categories to save my time every day.
    4. I want to create a marco to set up the print area.

    Are all possible with VBA?

    Looking forward to attend your VBA course, but hope you can help me in the mean time.

  • Hello

    I have a spreadsheet that tracks tasks. There is a reporting process that passes from one person to the next. Once they complete their process they will change the drop-down from “No” to “Yes”. In the first column, I want to show who is the current owner of the process. The macro I can vaguely see is:

    If column N says “No” then column A = column I.
    If column N says “Yes” then column A stays blank.

    Move on:

    If column Q says “No” then column A = column I.
    If column Q says “Yes” then column A stays blank.

    Move on:

    If column V says “No” then column A = column H.
    If column V says “Yes” then column A stays blank.

    And so on….

  • I have data with 30 and 40 columns in 10 sheets.
    I want to filter 1 column with one name and delete all others in all the 10 sheets