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,548 comments

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

  • Hi Jon! I would like to automate creation of monthly financial reports which are consolidated from different data files based on given criteria (location, type of work, etc.). It is pretty boring copy-paste work that takes too much time… Your first video makes me hope I can make it 🙂 The way you explain VBA is simple and easy to understand, thank you!

  • I would like to automate the stock control and HACCP systems for my small business.
    Thank you for a great tool to learn VBA.

  • i want to know when to make spaces and when to use the under score symbol??

    Regarding what i want to automate is a cover sheet for a detailed revenue work sheets.

  • For me it is very good to learn/refresh the basics of macros/VBA.
    Your videos seem to be what I need. I also have VBA-manuals but I prefer your step by step approach.
    I need macros so that I can work with loops a.o. for analysing LOTTO-results.

  • Hi Jon, I have two processes that I’d like to automate. One is, I’d like to automate a join between two sets of data. The two datasets can either be placed in two different tabs in the same workbook, or, pulled in from SQL server with 2 separate queries. They’d have a common primary key. The join would pull specific columns only. The other process I’d like to automate is to extract and isolate in a new tab all entries from another tab that have a specific common word or string in a certain cell range. Do you have any tutorials on these? Thanks!

  • i want to make an automated invoicing workbook wherein i can insert the data like customer, supplier, products, product codes, price,tax, etc. and also can get reports in several forms in accordance to need of taxes, codes, summary etc.

  • Hi,
    So nice to see your tutorial video part-1. I hope I get good insight from seeing the other 2 ones!
    I have just started using VBA as I came across a project requirement at work.

    I want to create a database which has 4 columns :
    ProjectName (to show a list of projects),
    ResourceName (to show a list of employees),
    Year and
    Month.
    To enter in those categories, I have 10 projects and 4 Resources that I need to spread repeatedly for 2 years (starting from 2018 say), for 12 months in each year.
    So the resulting database will have:
    10 Projects *4 Resources*2 Years*12 Months = 960 lines of Database.

    • The DB you have mention will need a motor more agile.. I will suggest you use MsAccess.. Excel can manage this project, but will have to use all it’s resources to maintain it once it running.

  • I am a beginner, so I’m having troubles understanding.
    my first project: is downloading 2 checking accounts, data, adding an type of expense to charge to, produce a pivot table for each (summarizing the account month & year. then adding or merging the 2 together. Results is the total of expenses for both accounts. Add the next 11 months as created and summarize The complete year by month. Future options will be adding tax lines!

    Would like to become an Excel programmer applications at local company!

  • Hi
    I have created many (relatively simple) macros without training by doing test runs and then diving into the VB editor to make changes. Quite simple when trying reproduce repetitive tasks. However I wish to take it further as much of my job is about data manipulation.
    This first section is simple but there were quite a number of tips and explanations which I found really useful. It has to be said that the video is clear, concise and in easy to understand language, which is not always the case with computing tutorials.
    I very much look forward to the next ‘episodes’.
    Slightly off thread, do you do similar courses for MS Access?

  • Hello
    I started out creating a macro that imported data and re-arranged it fo easy reading. I assigned a Ctrl-X to initiate the macro (for non-computer wavy users). But then, I wanted to add a “button” for easier use and could not figure out how to add this button to their personal worksheet. Your videos for adding these actions to the menu bar are great, but is there a way to add the addition of a button for them to click on after the import macro functions?
    Thank you,
    Tom

  • I have office 365; work primarily with Mac, but interact with Windows clients as well. I have both systems, W-10 through Parallels on the Mac, and Mojave. I am very interested in the differences. I have ordered your Essential Skills for Excel 2019 for Mac. Also interested in automating a purchase order from an inventory with nested advanced filters and sorts. Have watched several of your videos, found them to be very appropriate for me, I am pretty new, but do okay with self teaching with good materiel… Thanks, considering your course on VBA programming at later date…

  • We have a sales workbook with multiple worksheets. If a “Y” is entered in column B, I need to have cells D-I in that particular row copied to another worksheet in the workbook.

  • I just wanted a refresher from the free seminar you gave last week as I haven’t used it since and I wanted to try this weekend. I’m going to attempt to do the Table of Contents with the hyperlinks you did last week. If I can’t, I’ll be watching video 2 again while I wait for mgt. approval for your course. Thanks again, I’m a bit older so the ability to go back to your videos is a huge selling point for me. Tricia

  • Jon,
    Thanks for the video on Macros and VBAs; I have to relearn about macros for a potential job. I learned these tools years ago but since I haven’t used them, I needed a refresher.

  • I want to write a macro that copies the values in a sheet and then sorts the data on each line individually for all rows in that copied sheet (the data to be sorted will be in the range D2:AA500

  • I am reviewing your training in order to learn how I can sort a column of IP addresses correctly, where IP address 10.10.10.11 will fall above 10.10.10.100. I also need the that sorting function to “Expand Selection” so that the reset of the sheet follows the IP in the same row. I work with IP addresses and MAC addresses all day long so being able to sort them properly will save a great deal of time.

    Thank you

  • I’m new to VBA and found this great instructional video of yours very helpful. I’m looking forward to the way you teach this course which is very easy to follow. Thank you.

  • Very good and detailed explanations. I think this helped me finally out of some problems in understanding what objects is all about. Thank you!

  • Hi Jon, I have two Excel first excel is main file and second file currency change rate. How to convert currency (USD,INR to GBP) format.

    First excel: Main Excel :

    Empno Ename Amount Currency Total Amount Month Country
    100 Sam 20000 INR Convert Currency Nov’18 United Kingdom
    101 Ding 1140 USD Convert Currency Nov’18 United Kingdom

    Second Excel : Currency Rate excel :

    Date USD EUR GBP
    11/10/18 66.34 73.45 93.85
    01/11/18 65.32 74.34 94.45 only pick 01/11/18 rate
    12/11/18 66.23 77.34 95.34

    Every month 1 date pick currency rate and convert currency in main file

  • Hi Johan,

    I have one question?, I have 2 file first file is main file and second file currency rate, first time check date in currency rate excel if every month 1 date currency rate 98.23 this value is locked and convert (INR,USD to GBP) format.

    Ex: first excel:
    Empno Ename Amount Currency Total Amount Month Deptno

    100 Sam 20000 INR Convert value Nov’18 20
    101 D 1140 USD Convert Value Nov’18 30

    2: second excel

    Date USD EUR GBP
    21/09/18 62.8 77.34 93.54
    01/10/18 66.3 76.45 94.32
    18/10/18 67.4 77.34 94.3

    Only capture every month 1 date currency rate excel and convert INR and USD currency to GBP format in Main file (first excel).

  • Jon,

    I’ve enjoyed your YouTube excel video tutorials. I’m interested in taking your VBA Pro course, but must have missed the sign up. When do you plan to offer it again? Thanks so much.

    Respectfully,

    Curt

  • Greetings.

    Thank you very much for the above video, very informative and easy to understand. I am working as an equity analyst and use financial modeling extensively. I would like to know more how macro & VBA can be used in financial modeling. I would be grateful if you can share some basic educational videos on it.

    Thank you once again and have a great day.

    Regards.

  • I am creating a spreadsheet to keep track of loaner vehicles for a client. I am trying to use VBA to automatically duplicate loaner vehicle entries automatically for customers who need a car for multiple days.

  • I want to transfer item numbers and skus from an invoice sheet that I’ve created to my out inventory sheet. my invoice where the data is located is not formatted as a table but my out inventory sheet is. I need for the blank cells from the invoice sheet to be ignored and for the transferred items to be added to the list following the previous entry. all done with the click of a button located on my invoice sheet

  • Hi Jon,

    I have a column and in each cell there is some variable length text which is followed by a / and there after again some variable length text. I want to get the text behind the / moved to another column and deleted in the original column.

  • Hello Jon,

    First, I am looking for way to automate the copy and paste of data from excel files containing data downloaded from a mainframe financial application in xls format into a workbook containing a sheet for each department in our division to create budgets annually. These need to be distributed to the department heads to verify, update and correct before being returned. Once returned,the information will be consolidated and averages and projections will be applied with supporting commentary and they may be interrogated for ‘what if’ scenarios as needed throughout the year. Secondly, I am interested in consolidating data obtained from a vendor application that is downloaded in csv format and massaged into reports and charts showing revenues and distributions for several years that can be uploaded onto a website. A dashboard format might even be useful for these reports.

  • Hello Jon
    I am trying to set a PASS or FAIL status in a cell that needs to look for parameters within a group of parameters for example,

    1 values between 342 to 418 PASS above or below FAIL (Target number is 380)
    2 Values Between 360 to 440 PASS above or Below FAIL (Target number is 400)
    3 Values between 369 to 456 PASS above or Below FAIL (Target number is 415)

    Thanks you for your advice

    Kind Regards
    Savio

  • To be candid, your presentation of the class tutorial video has really change my perception about learning excel from impossibility to possible. I think this video is bringing me back on track and is nothing but making learning VBA easy without stress.
    However, the project I have at hand is writing a vba code for processing of large volume of combustion data. I will be glad if this target could be achieved via the knowledge I will garner from your tutorial classes.

    Thank you.

  • Hi Jon,
    nice video. I would like to extract colored sections of text out of cell which contains plain text and differently colored sections. Each color will have its own destination. Have you done that before, can it be done?

    Best regards,
    Peter

  • Hi,

    Would like to ask some vba code for below:
    1.How to copy an opened excel file with today’s date in its name into another workbook? As i created vba for “Browse For File” to open an excel. eg: Windows(Payment Dated 12 Nov 2018)
    2.How to copy filtered data (from range A to range C) in a workbook into another workbook?

  • Jon:

    I enjoyed looking at the first video on Macros and VBA and must say that I would recommend any of your videos & classes to anyone. I haven’t received the links to the workbooks you were using in that session and was wondering if you can please sent it to me. Thanks and hope to join you again.

    frank

  • Hello Jon,

    I am usual copy cat with vba codes and then just amend to my needs. I am trying the copy a value from column b. Some cells are empty and i want to exude them. Then all that past completely different excel workbook sheet. I will have a button in Workbook(“BOOK2.XLS”) this what i would expect to look.

    Workbooks(“BOOK1.XLS”).Activate

    LastRow = Range(“B65536”).End(xlUp).Row
    For i = 1 To LastRow
    If IsEmpty(Cells(i, 1)) Then
    Cells(i, 1).Copy
    Workbooks(“BOOK2.XLS”).Activate
    Range(“B2”).Select
    ActiveSheet.Paste

    End If
    Next i

    Thank you so much

  • Dear Sir, when I watch this video and at least able to create My First Macro File. I notice that there is a file (VBAProject(EC_Tab_Hound.xlam) I tried to create im my file but I coudn’t find xlam extention. So I need your advise.

  • Hey Jon,
    I was able to boss to spring for the VBA for Excel course. They went to register me today and found that enrollment was closed. Please let me know when the next class is scheduled and/or what are my options.

    Thanks.

  • Hello Jon, I would like to automate the posting of values from a FTP server to an excel report.
    The values are : Date, Time, and Gallons, and the server creates .CSV files with this information.
    The gallons value is a process reading automatically updated once a day, it needs to be recorded and
    stored, therefore the next days reading must increment to the next row of cells and so on.
    At the end of the week a new worksheet must be opened, and the process repeated with a new work
    sheet every week, for 52 weeks.
    I’ve experimented using the Windows scheduler to trigger a .VBS file from a .bat file which runs a Macro
    in order to email the report, and that works well, but does not complete the requirements for whole
    process, as you can see.
    Any recommendation would be greatly appreciated. By the way I have had a VBA course in the past but
    it was probably in 2005 or so , mostly dim awareness at this point, as I haven’t used it.
    Thanks in advance for any help you can provide.

  • Jon for some reason today when I tried to download the second free video I get a thing that says javascript but doesn’t download Any ideas why this is happening Thank you