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!
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
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.
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.
Dear Jon,
When you will send me a new video?
Thank you in advance,
Lazar
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.
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
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
Very nicely explained! Thanks.
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?
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
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.
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
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.
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.
Thank you for a well done and informative webcast on Excel VBA. I look forward to viewing more!!
Nicely done, thank you!
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
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.
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.
Jon, thank you so much for sharing this video. It’s very easy to understand and apply!
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
Hi,
This is really helpful, your concept of explaining is very good and simple to understand.
Regards
Manoj
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).
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
Very good and detailed explanations. I think this helped me finally out of some problems in understanding what objects is all about. 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.
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 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
Hi Jon, Awesome. Expected nothing less
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.
Thanks John
Well Explained Basic explained in first video.
Thanks again.
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
Messy Data Cleaning
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 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…
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
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?
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,
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.
Okay, can you suggest an Sql query to start with?
Thanks a lot.
Very informative video. Simple to follow. To the point. Thank you
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 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 really want to learn to create macros for a job opportunity.
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.
report sheets
I want to tell excel to look for a date and copy the rows with that date to another workbook.
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.
Practically broken down for baby steps to writing VBA code.
Great work; keep it up!
I would like to automate the stock control and HACCP systems for my small business.
Thank you for a great tool to learn VBA.
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!