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!​

Click Here to Leave a Comment Below 1305 comments
Yolanda Guerra - December 12, 2017

Nice intro video.
I have an excel sheet that has some part numbers and pricing. However the prices are offset down one cell, what I want is to bring the prices up on the same row as the part numbers and get rid of the now blank row that they were originally in
So I have this:
47-5477
669.99 EA
47-5479
651.49 EA
47-5271
50.99 EA
47-5275
47.49 EA

I want this:
47-5477 669.99 EA
47-5479 651.49 EA
47-5271 50.99 EA
47-5275 47.49 EA

Reply
S K BATRA - December 10, 2017

HI, I have worksheet with (In Header) Name of Share, Date of Purchase, Rate, Amount, Brokerage, Date of Sell etc. I want Macro to 1) segregate shares by company Name in Separate Sheet.
2) Do the calculation of Profit/Loss in terms of Days of Holding and Annual Profit/Loss i.e annualised %age return & holding return period.
3) No of shares of a particular company held as on date and on a particular date.
4) No of shares of a particular company held as on date and on a particular date in my name, my wife name, my child name.
5) No of shares of a particular company held in separate Bank as on date and on a particular date in my name, my wife name, my child name.

IT WOULD BE GREAT GREAT GREAT HELP TO ME.

Reply
Laura Lynn - December 8, 2017

Hi,
What I would like to automate in my workbook is the ability to navigate the user to specific cells to enter data, not let the user move to next cell until they enter data in this specific cell (have a message pop up explaining they need to complete data before moving on) and finally, navigate the user over cells that I do not want them to populate.

Reply
Khin - December 7, 2017

Thanks and I just start learning Macro.

Reply
Mark - December 3, 2017

I would like to create a macro that will insert formulas such as MIN() in blank cells across a number of columns when there is no set pattern to the blank rows. Eg. Row 3 is blank then Row 7 is blank and then row 16 is blank.

Thanks for your help

Mark

Reply
Tina - December 3, 2017

Fantastic well explained. Wish you too were Word geek because I have some bookmarks I’m going to copy into an excel document. I’ve seen one of your other videos where you use find the last row … etc.
Do you have a suggestion on how it can be done?

Reply
Ernest - December 1, 2017

I have been using Excel for a long time but never tried to learn how to write macros. I am developing a Excel application and need to automate a process. I will be great to learn VBA so I can write my own macros.

Reply
Zeinab Atef helmy Ali - November 30, 2017

This great.. thanks

Reply
Alfonso Rodriguez - November 28, 2017

Excelent video ! There couldn’t be a more complete, concise and engaging Excel Macro Primer.
This is a goto site on my Bookmark, and I will surely visit each week.
Thanks !

Reply
Antwaun - November 27, 2017

Great very easy to follow looking forward to the next video… Thanks.

Reply
Piti - November 26, 2017

Great intro course

Reply
Srihari - November 25, 2017

Great Video, Jon!

I got to learn the basics and your videos help me save time at my work!

Please continue to post such videos.

Reply
Dr. Shazia - November 21, 2017

Nicely Explained and the best video. Thank u v much Jon

Reply
Alex - November 16, 2017

Need to automate several reports from work. This is really helpful.

Reply
MS - November 15, 2017

Great lesson. Clearly explained and demonstrated. You have me from now on as one of your fans. Thanks

Reply
Martin - November 11, 2017

Great Thanks – Clears many basic issues I was not aware of!
Would like to automate the priorities of my tasks
I have for three different Projects
I have three worksheets (Each WORKSHEET contains my tasks for each on of my Project)
Each Task is a line item with columns indicating info of the task, and mainly the Priority, Category and various dates
I want to extract the Task which has Priority “A” to a new Sheet called “A”, and extract the Task which has Priority “B” to a new Sheet called “B”, etc. for all my Priorities up to “G”
The Columns headers / titles need to be on the new Priority Worksheet as well
This must happen for Project 1,2 and 3 (Therefor I will have all the priority “A” Task line items from my three different Projects in one Worksheet for ease of getting through my priorities.
Daily I will return to each Project Worksheet and either delete the task when completed or alter its Priority code – Once I have run through all the projects, I want to run the Macro, then I have my Priority types all in one spot !
I trust this makes sense?
Thanks and regards
Martin

Reply
Vin - November 11, 2017

I have a list of 100’s of suppliers (refreshed monthly to ensure new accounts are added) – (Column A = Supplier code & column B Supplier Name) AND I have a monthly Supplier Reconciliation Template that comprises basic sub & totalled areas [Supplier balance, less items under query or missing doc’s, credit notes, outstanding payments and items actually to be paid, resulting in reconciled Bal per Supplier a/c ledger], with opening balance per supplier statement and closing balance of payment due for the month as per my Supplier account ledger, which supplier code, name & total due to be paid must be carried into a summary sheet and sorted alphabetically referenced to the specific month.

Required:- to create monthly, worksheets only for relevant suppliers this month concerned and point the payable balance to a summary sheet saving time/effort manually effecting the time consuming creation of a Suppliers Control by selecting a macro button whenever a new supplier recon is required!

The sequence needed is to search the name of suppliers within the supplier list, find the relevant supplier name (considering duplication in similar names)by using an ‘input query’ into the macro, then creating a new worksheet by copying the template worksheet and renaming this worksheet with the suppliers name as found in search above, renaming worksheet, then filling in the supplier name and code into the relevant fields in the template and finally creating a reference to this worksheet by supplier name & code in the Summary Sheet and pointing the payable reconciled balance per template to the Summary sheet, which Summary is totalled giving the value payable for all suppliers for that period.

The specific clerical data can then be entered into the newly created reconciliation template and confirmed and a running total of payment due within cash-flow budget can be seen at all times as in e.g. a Dashboard effect as well as a summary list for print & record.

Reply
Dea - November 11, 2017

to be able to create a macro to expedite processing huge amount of data

Reply
Marwa - November 10, 2017

The video is really very useful . I haven’t used excel VBA before & I am not a professional at using excel , but I am supposed to make optimization & I found it easier to implement in excel than Matlab .

But , excel solver add ins only provide optimization using 3 methods : Simplex LP , GRG non linear & Evolutionary GA .

But , I need to implement optimization using other algorithms that are not found in excel solver add ins like ACO Ant colony & PSO particle swarm optimization methods & I found that I can get their excel VBA code & use them in excel &
I don’t know how to do this … so any help please concerning this subject ??

Appreciate your feedback .

Thank you very much .

Reply
Steve - November 10, 2017

I am interested in automating the addition of a worksheet to a spreadsheet that contains not only static data fields and formulas, but also has a number named ranges and other references some of which are constructed through concatenation of a portion of the name of the new worksheet.
This would be a good place to start.
Right now I just manually create a copy of the prior months worksheet, and then I need to manually change the new sheet name and then the names ranges in the Name Manager.

Reply
Dave - November 6, 2017

hi,

i handle the budget preparation of my company; i find it difficulty to consolidate various sheets and establish the link thereon..
hope you can assist me on that.

thanks a lot..

//dave

Reply
yariv rotem - November 4, 2017

Its my first time in vba world, and the first video help me to understand the base of vba.

looking forward to the next video.

Reply
Luuk - November 3, 2017

Over the last years I have used Excel and Access for my work and wrote a lot of VBA code in the process. Sadly the firm where I worked went bankrupt and I lost that job. I have found a new one for the time beeing but I would like to show of my skills by means of a certificate and I hope to reach that goal with this on line course.

Reply
Emmanuel - October 25, 2017

Fantastic! Jon.

I have multiple rows and multiple columns and would want to concatenate the cells on each row. E.g. concatenate values in column A, B, C of row 1 and put in cell R1D and do similar for subsequent ones

A B C D
R1 2 5 10 2.5.10
R2 1 3 8 1.3.8
R3 3 2 8 3.2.8

The number of rows varies depending on information collected. I trust you would assist

Reply
John Kulnane - October 22, 2017

I want to change a cell based on user entry to a cell. The problem I have is that I’ve written the VBA code to use the Change event but Excel asks for the name of the macro. I thought the code would just execute. How do I tell Excel I want to use the module that I’ve created? Thanks. I really like your videos.

Reply
Ossie - October 19, 2017

I wish to gain skills to build from,scratch, a robust and usable Excel based Desktop application to be used by others without any excel skills

Reply
sriram - October 19, 2017

Hi jon,
As a finance professional, i would like to automate few tasks like hide gridlines, copy the company name as heading to every sheet etc.. these are repetitive in nature and consumes lot of time.

Reply
Sherly - October 18, 2017

I really interested in learning more about excel all about excel, and I’m so glad to fine your website, I’ve been struggling in learning some about excel that I can’t understand. I’m creating some tamplate and I want it to automatically synchronize with other Excel n work sheet but I still lack in knowledge of excel program. Hope you can advise me to solve my problems.
Thank you

Reply
Ginny - October 10, 2017

I would like to add columns to a spreadsheet that is updated monthly. Currently I hide the oldest, copy and insert the newest, delete contents of the copied column and paste in the new information. There are 16 metrics side by side and the same task must be done for each.

Reply
keshav - October 9, 2017

i want to upload the contents of my excel sheet into a website, one cell at a time.

Reply
mohd basir bin suleiman - October 8, 2017

As a teacher I have say 60 students and I need to list :-

1. Each student as to Grade (eg 50 should return the alphabet C in the neighboring cell.(A, B,C etc)
2. A summary as to how many students gets A, and how many got B etc.
3. And a column graph as to how many students got A, B, C etc.

TQ

Reply
Leanna - October 6, 2017

I am responsible for maintaining budgets at home and at work. I’d love an easier way to do that.

Reply
Mike - October 3, 2017

New to VBA & Macros. I have data in several cells on a work sheet that move based on the =Today() function and I want to be able to move that same cells info to another area of the same sheet so as it disappears via the today date function the data is still there.

Reply
Andrea - September 29, 2017

I want to automate matching lines to combine them. for example, on a bank reconciliation, I want my macro to look at two particular data columns for a match, then consolidate the lines. Love Andy!

Reply
Lou - September 27, 2017

Easy to follow. Looking forward to next video. Would like to automate hiding/unhiding blank rows and columns. Would like to automate monthly report printing.

Reply

Leave a Reply: