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:
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.
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!
The processes that I want to automate are all of the mundane and common processes, saving time, and leaving room for all of the creative and fun things.
I want to automate the analysis processes. Like Chi-square test, correlation analysis, and so forth,
Hello Jon
First thing the Videos are great and some of the easiest to understand and comprehend that I have Found.
Thank you
Very much for your time and effort to put this site up.
The processes that I want to automate is
How to make a rolling number counter for a cell in excel 2016 that will show numbers rolling by from a range of ((sample low (1) to high (500) taking (xxx) time to complete))
Thank You
Chuck
Thank you Chuck! I’m happy to hear you are enjoying the videos. I don’t think I fully understand that process, but I’m sure it is possible to automate with VBA.
I want to automate reporting proces
Hi Jon, I’m working with my supervisor to create a macro which inserts a formula we use and then copy it down the entire column to the end of the last row of data.
I’ve found a way to do this, but I’m also stuck. This formula is based on the number of rows divided by the sample size. These variables change with each workbook, so what I need is a way to enter those two numbers and then run the macro to insert the formula, make the calculation and copy it down the column.
I thought about an input box but that’s about as far as I got. This seems really complex, so let me know if you need the method I found which does work. I just can’t get past it to make the whole thing workable.
Hi Steve,
I do have a video series on how to find the last used row or cell in a sheet. Those techniques will return the result to a variable that stores the row number value in memory while the macro is running. You can then reuse that variable in formulas for the range references. Here is a very simple example.
lRow = Worksheets("Sheet1").Cells(Worksheets("Sheet1").Rows.Count, 1).End(xlUp).Row Worksheets("Sheet1").Range("B1").Formula = "=SUM(A1:A" & lRow & ")"The first line finds the last row of column 1 (A) in the sheet use the Range.End method. I explain that method at the link above.
The second line inserts a SUM formula in cell B1 for the range A1 to the the last used row in column A, which is returned to the variable lRow.
I hope that helps get you started.
I have a three worksheet workbook. the data in “Sheet1” will get either a “Yes” or a “No” in column “F”.
An entry of “No” should automatically delete the entire row that had the “No” entry.
An entry of “Yes” should automatically copy cells “A2:B2” and paste them into the cells in columns “B” and “C” in the first empty row.
Data in “Sheet2” will get either a “W” or “L” in column “E”.
An entry of “L” should automatically delete the entire row that had the “No” entry.
An entry of “W” should automatically copy cells “A2:B2” and paste them into the cells in columns “A” and “B” in the first empty row of the worksheets that correspond with the person’s name in column “B” of “Sheet2”.
So far I have a working macro that deletes the “No” rows in “Sheet1”, but only when I press a ‘button’ I placed on the sheet. I can’t figure out how to automate it.
I can also get it the copy data in “Sheet1” to a different location on “Sheet1” but I can’t get it to do it to the different sheet.
I spent over $100 on VBA books and still can’t get this to work. I’ve spent HOURS on this project and I can’t get anywhere.
Hi Alan,
In regards to getting the macro to run without pressing a button, you can use a worksheet event for this. I have an article and video that explains worksheet events in more detail.
I have a few other resources that might help including a video series on how to copy & paste with VBA and an article on how to hide columns that contain a specific cell value. You could change that code to delete rows/columns instead of hiding them.
I hope that helps. I know it can be frustrating when you are getting started with VBA, but try to hang in there and take it one step at a time. Thanks! 🙂
I love your video. The only thing is can it slow down just a little. I find it hard for me to grasp when its so fast. I would like to know how to saveas a file give it a name then go to original file and clean it up. How to make fill-in forms quickly and the data go to a spreadsheet.
Thank you Diane! You can slow down the video by clicking the gear icon in the bottom right corner, then change the Speed setting. You can also pause the video as much as you’d like to follow along. I hope that helps. Thanks again! 🙂
Hi,
I am working for Shrimp Farming company and want to help my org to analyse the business growth. And i would like to create a format which help down layer users to do their daily work in minimal time frame.
So that i am looking the solution with Pivot table, Macros and VBA course.
I trust, i have reached to the correct place to get the knowledge.
Thank you
Vishal
Thank you Vishal! 🙂
Nice presentation. This will help me be confident to start using vba macro’s.
I have to automate the collection of data from .cvs files and other xls workbooks
Thanks that was great! I am feeling much more confident with VBA macros and some of the code I copied from the web.
Is it possible to write a code that would allow you to program a set number of columns to populate with “N/A” if only one or two of those columns are populated by the user? So if you have titled columns A-J with TVs, DVD player, phone, etc. and they populate only the TV column, can the action tell it to populate the rest of the columns in that row with “N/A”? (on set columns). Sorry if this is confusing.
Hi Paul,
Yes, this is possible with VBA. You could have a macro that checks if the cells are blank, and then populates those cells with the “N/A”. The macro can be run automatically when the user changes a cell using a worksheet event. Here is an article on how to run code based on user events. Thanks!
Just thanks a lot…..
Thanks for the video, started to understand more the process, explation was great.
As I do create plenty of reports and many of them are already connected to a db and updated automatically, now I would like to create such macros so that my charts & graphs in a ppt presentations update as well this would save me a huge amount of time to copy and paste data to my tables, charts & graphs..
Thanks,
Stephen
I stumbled on to this course by accident when looking for help on another topic. I was able to look a a description of this at work where Office 2016 is in use. I do not have the time and permission to spend enough time to complete the course at work so I joined the newsletter at home. Unfortunately here I use Mac. When I opened the file My First Macro.xslm I got “Visual Basic macros do not work in Office 2008 for mac. To open the file and remove the macros , click Open and Remove Macros.”
I am not adverse to upgrading if it will accomplish our goal: learning Excel and writing macros in Excel. What suggestion do you have?
Hi Stan,
Unfortunately, the Mac 2016 version of Excel does not have a solid VB Editor yet. Microsoft has said they are planning to update it, but not sure when that will be. Here is a link to where you can vote for the feature on the Excel Uservoice site.
https://excel.uservoice.com/forums/304933-excel-for-mac/suggestions/9098431-bring-back-a-fully-functional-vbe-visual-basic-ed
I recommend using the Windows versions of Excel if you are interested in developing macros and applications. I hope that helps.
Hi,
You have a nice way to present.
Like you, I also like Excel very much.
Going through the video “My First Macro”,
I am growing more confident about the
puzzling VBA Coding.
Thanks a lot…dr prasad
Hi Jon
Let me first start by saying you run a fantastic programme, thank you for your efforts, I’ve learned a lot.
I’m having a problem with running the macro as you’ve shown in the above video, I’ve entered the code in the VBA window and when I hit ‘F8’ to run the macro an error message pops up; “Compile error: Sub or Function not defined. Can you please help me to get this right.
Regards
Mthoko Meyiwa
Hi Jon,
First of all thank you very much for your videos. These tutorial videos are very informative and I’ve seen many of your videos.They are really increasing my knowledge in excel.
My concern is that I’m unable to get your second and third videos of VBA & Macros.
So,please!!! send me link of other videos as well.
Warm Regards:
Rahul
You’re a great presenter! I appreciate the time you took to put these videos on, they’re very helpful… thanks!
Easy to listen to and follow along. Thx for a better understanding of macros.
Cheers,
Scott H.
This is excellent! Thank you for showing all the little shortcuts, too! Just subscribed. I am excited to get started in sharpening my excel tools. Thanks, Jon!
Hi Britt,
Thanks for subscribing! I really appreciate your support. 🙂
Respected Sir,
I am very excited to watch the “How to Write Your First Macro” video, want to expert very soon under your kind assistance.
Great video on to number 2.
Can you do one on forms to find date and insert the results to a new worksheet.
Thanks
Albert
Great Start for me, new to all this. Video explained a lot I don’t really understand. Thank You
I am trying to copy data form one sheet to another sheet.
Copy selected(highlighted row) from sheet1 to sheet2
start copied row at row 5 sheet2
be able to add more lines to sheet2 without overwriting or deleting data already copied
maintain all copied data on sheet2 unless I move to another sheet, say sheet3 or delete it myself
Hi Ron,
Thank you for the nice feedback. Great question! I have another video series on Copy and Paste with VBA that will help you. One of the most common questions I get is about how paste data below the last used row on a sheet. So, I created a video that explains how to write a macro for this. You can get the video at that bottom of that page.
I hope that helps. Please let me know if you have any questions. Thanks!
The process I would like to automate is:
copy selected row sheet1 to sheet2 , start copied data in row 5
be able to continue adding rows as needed without deleting or overwriting what has already been copied
Great first tutorial! I’m excited to learn more. I need to have a flexible capability to re-format spreadsheets so that the data is in a format which can be imported into our DB.
thanks!
To create a Pivot table in excel vba containing multiple row field without total in tabular form
Great first tutorial! I’m excited to learn more. Can add samll dashboard projects with code n tutorials + vedio so that we can learn more. Plz do.
Thanks for the intro. I’m eagerly awaiting parts 2 and 3.
I want to use macros to gather data from an unknown number of spreadsheets with data structured in A4 layout and collect it in a master spreadsheet in another workbook with data from each original speadsheet organized in a column.
Thank you Hakan! That is definitely possible with VBA. I actually have a video in my VBA Pro Course that explains how to consolidate sheets like that. We can use a For Next loop, which you will see in video 2, to loop through all sheets in the workbook.
Hey Jon! Great first video! I learnt quite a bit and it has intrigued me about the various possibilities within excel. I am looking forward to receiving the link to your second and third video!
Hello Jon, you have a great way to make all of this coding stuff look easy, now I know what a Microsoft MVP really is..
I look forward to the 2nd video.
Thanks for helping out on this.
Thank you Rafael! I really appreciate the nice feedback! 🙂
Hey Jon, Thank you very much for the introduction to VBA scripting, i loved your explanation. I will be going through rest of videos to get more understanding on the scripting procedure.
Meanwhile i have specify case for which i am looking for the VBA scripting. My requirement is : I have a work book with the master data and i need to copy some data from the temporary created workbook to master copy. Logic is to compare the cells in the column 1 of the both the workbooks if found any cell is matching then i need to copy the value from the column 5 of that particular row to column 4 of that particular row in the mater work book.
Thanks & Regards
Dilip Reddy N
Thank you for the nice feedback Dilip! 🙂
I’ve been playing with VBA off and on for several years. As my macros get more complex (i.e., what I copy from the internet and tweak to my uses), I’m finding many glitches with my tweaks. While I struggle to get them sorted out, I usually do, but it takes much time. Starting with a basic training should have been my first step, but I’ve always taken a jump right in” approach! I’m hoping the basics will help me understand better what I’m doing.
My current project has ±200 worksheets and some of the macros take a while to run. I’m hoping to learn a bit about how to optimize the code – at least from a basic level. I watched a couple other videos and have finally grasped the copy-paste methods (I’ve often read online that you don’t need to select cells, despite what the macro recorder tells us!).
Does the next video link appear after I post this comment?!
I want to apply payments to multiple sheets in a workbook.
Great Video and looking forward to learning from you
i FOUND THIS VERY GOOD
Hello Jon,
I’ve been using macros only for recording some repeated action, but somehow when the action goes wrong by mistake I have to re-do it to get the correct repeated action.
Is there anyway to debug such errors or mistakes ..?
Thank you in advance, really appreciate if I had a chance to get the other vid link
Hi Jon,
Loved it! Clear and concise explanation on VBA for Excel. How do I get next 2 videos?
Thanks, again!
It is a great simplified way to understand Macros.
Great first video.
I want to create a macro to copy and paste the values in a range of cells based on the month and a specific day of the month.
In addition I want to then protect the pasted data.
Thanks
I want to automate the input of data for invoices, the ordering of materials, as well as job parameters and tickets.
Hello,
I am new in excel VBA and my job is require the skill to data analyse, could you please advise me if I need to calculate the data as following formula how I do the correct code
Example:
dim r1 as range, r2 as range, r3 as range, k as integer
set r1 = Range(“A2:A10”).value !The range contain some data
set r2 = Range(“B2:B10”).value !The range contain some data
set r3 = Range(“C2:C12:).value
set k = Range(“E2”).value !This range can be input any value as co-efficiency
r3 = (r1+r2)*K
and the data is import from other spread sheet.
Thank you in advance and appreciate on you help.
Regards,
Bill
Not sure. Just looking to enhance my skills
Hi Jon – in my line of work I encounter a lot of Time data, like a time interval report. Copy & Paste some ranges of data to a different workbook and create a summary by adding the copied Time data. Hope you can help me this. 🙂
BTW. I admire people like you who are sharing their knowledge to beginners. I just encountered your site yesterday and I can say that your explanations on what and how the processes involve on your tutorials are the best I have seen. Have a good one!
Hi Martin,
Thank you for the nice feedback! I do have another video series on 3 ways to copy & paste with VBA. This should help get you started with the very common task of automating copy/paste tasks.
Thanks again! 🙂
Hi Jon,
I am tax consultant where i have work on excel spreadsheets on a daily basis. Could you please suggest me some books and courses which will be helpful to enhance my knowledge in VBA macro programming.
Thanks in advance,
Swarup
Hi, I’m an accountant and I want to explore the possibilities that Excel-VBA has to offer concerning the data I retrieve from my accounting software.
Great and clear lesson
Excellent training Jon! I have been watching many videos, but yours is the BEST I have seen. Your way to explain is just terrific. I was able to follow and understand all you said. Thank you for such great work.
Thank you Noemi! I really appreciate the nice feedback. 🙂
1st of all my English is poor so please try to understand me
I want make 1 automatic entry movie shhet based on match serial number
Example
I have made 1 workbook there 3 sheet in that book (1)In-house(2)Dispatch (3)Data entry sheet, I want to do that if I enter serial number in “data entry sheet” cell “P3” then it will go to search in “In-house” column “A” and If serial number match then it will show me in “data entry sheet” cell”P5″ then if I run macro matched entry of “in-house” should move in “dispatch” with all cell of matched Raw (example :- If match Raw “A5” then move cells from “A5:W5” in “dispatch” sheet
Jon,
As I get more polished on the advanced basics, I know this video series would be extremely useful for running end of year and quarterly budget reports. For now, these lessons are a little to advanced for me at the moment but loving your other lessons! Keep up the clear and concise videos. I, like so many, really appreciate what you are doing.
Hello Jon..!! You are really a great person with treasure of knowledge and distributing with others. Big thanks.
I am new learner and your way to make others understand is such a easy method/short cut.
Respect !!