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!
Hello Sir Jon,
I am a fan of Excel and VBA in particular. I have appreciated this video because of its simplicity. I am even thinking of using its methods in a VBA class I am going to deliver in few days,in a physical classroom :). Could you please help me to provide a good class with good contents and efficiency.
Thanks in advance
Have ‘toyed’ with excel and cell formulas for decades (Civ. Eng. studies in early 90’s) but now want to use VBA to pull apart text strings (separated by ; in 1 cell) inside monster sized sheets of .csv CRM’s (Customer Relationship Mngt) with potential leads there to be had…if only I can get the VBA to insert rows and associate the multiple emails in one cell with its company in the cell next to it. I.e. delimit the email cell with 1,2,5,15 emails in it, then insert same number of rows below to paste each email and its owners name and company name. Tiresome to do ~100 companies… try 22k of them : /
I know it’s possible for VBA gods, can I be taught this?
Hey David,
Great question! Yes, this sounds like something that can be solved with VBA. As you said, there is some string work to be done with separating the emails by the separator into an array, then transposing that and outputting it down the sheet.
I don’t have specific training on this yet, but will add it to my list for lessons for the future. Thanks!
Excellent for Macros & VBA beginner’s level!!!
Thank you Mario! 🙂
I want to automate the daily production report. Now we have to manually copy the numbers and dates of production to track them.
Hey JC,
Yeah, manual copy & paste is no fun. I’ve done my fair share and that is what got me started learning macros. Sounds like a task you can definitely automate.
I have several xlsx files containing orders. Structure of all files is identical.
All xlsx files are copied to crresponding sheets in 1 new xlsx.
I want to add a new sheet on which all cells are added-up to 1 total per aricle / manufacturer (= by row).
I want to use VBA and not a function to make this easy to use for our staff.
All macro’s connected to a button on 1 xlsm.
Kind regards
Ludo
Hey Ludo,
That definitely sounds like a task for VBA. I do have some training in my VBA Pro Course on how to merge worksheets. You could also use a pivot table for the calculations once the data is combined. Or you could use VBA for that. Lots of possibilities with Excel! 🙂
Hi Jon,
How do I automate a financial system for a co-operative society where monthly contribution is made into a consolidated fund account, withdrawals can be made at any time, loan can be requested by contributors at anytime, and interest payment to contributors on amount contributed. Need your help
Read the whole entry. I made several attempts to record a macro and then modify it to automate a series of copies with no luck, all on the same sheet.
* I would be happy to get past Step (A) but it would be great to learn how to complete all three processes!
* Here are my three process with a progressing advancement in each one.
(A)- Copy 3 cells from A3:C3 to the current cell B1 { If that worked then …..}
(B)- Insert those 3 cells to the current cell B1 [Shift right]{ If that was working then add …. }
(C)- Automatically copy the 3 cells to and Insert them at B1 then advance to G1 ( 4 columns down because there is existing data in the and then repeat until last cell in ROW 1 or for the number value I would use in a counter loop
{ I have a current row of about 65 columns of data and I need to insert these 3 cells after each of the existing cells in that top row,which become my repeating headers. The rest of the data populating would not be as easy so this would provide a standard format that I could use in some Vlookup or HLookup formulas. I have done some macros and vba in the past.
— Any similar sample would be great, I learn my examples.}
Hello Jon!
I really appreciate your work! You are Really doing a great work!
I wonder if you can help me with this matter.
I am a university assistance professor, teaching classes with big number of students. I have designed a VBA to grade 15 question, MCQ Pop Quiz. every student has to log in and take the test. When the student finishes, I come after him and press grading key to get the grad. What I need is an excel VBA code to read all excel files and collect all the names, students’ numbers, and students’ ID number and sort them in one excel sheet
Do you have any Idea?
Thank you in advance
Dr Abdulhakim Sultan
Hello John,
Love learning about the potential of Excel. Really enjoyed the first video, looking forward to more.
Hi Jon,
First of all Thanks for sharing all of your Excel experiences and teachings through your tutorial videos, tips and shortcuts which is not only enhancing our excel skills in doing our daily work but also helping us to get recognised by our boss. Your Pivot Tables videos have really helped me a lot to receive that recognition. Thank you so much Jon Sir.
Really enjoyed your First Video on Macros & VBA. I might say A video worth watching for a Beginner to start with Macros and VBA. And yes I am a Beginner in Macros.
I wish if Macros & VBA can help me in doing my daily task of reconciling the Accounts Receivable of one company with Accounts Payable of another company via Invoice or Bill numbers, where the tons of data are stored in different Excel files. Presently I am using Vlookup to extract data from other files and sheets & Countif formulas to check duplicacies of Invoice numbers.
Is Reconciliation possible in Macros??
Kindly suggest.
Thanks & Regards,
Rahul
Very good introduction to Excel VBA. Very good pedagogy. I’m looking forward seeing the other videos.
Great Thanks.
I am intresting please
Hi Jon,
I really enjoyed the VBA webinar and would like to sign up for the VBA Pro class. Can you send me the link?
Hello Sir John,
Thanks for your assistance.
I want to create a macro and would require your help regarding the same. This is regarding the automation for Payroll. In sheet one I have detailed inputs for payroll which includes the Emp code,Name and other incentive along with the amount. In sheet two I have just mentioned E.code,Name,email ID & Months.
Now what require is when I type the employee ID the name, email Id automatically flashes in sheet 2 and at the same time when I choose the allowances it should populates figures from sheet 1 for each month.
I just wanted to know how can we use macros or VBA coding in this scenario.
Thanks & Regards
Ashita
Hi Ashita,
Great question! We can use Events in VBA to automatically run macros when the user takes an action like changing a cell. Here is a video that explains more about VBA Events. I hope that helps get you started. Thanks! 🙂
A1 B1 C1 D1 E1 F1 G1
Davy 340 Barnett Way San Antonio Texas 3435
I want to do c1&””&d1 in g1 and copy the value only back to c1.
Jon,
I need video’s 2 and 3 now. Thank you! Great video on VBA and Macros for beginners!
Excellent job of setting a baseline on the copy and paste functions. Understanding the basic operations of functions in Excel really helps as one moves on to the advanced features, functions, and formulas. I am not writing to obtain assistance in solving a problem, just wanted to commend you on the work you have done with Excel and especially with the production of the videos. This really shows your passion for educating Excel users, but your interest in making all of us Excel rookies and veterans better at what we do with Excel.
Keep up the good work and recognize that there are millions of of us that are benefiting from your knowledge and your willingness to share it with all of us. I am sure anybody who sees these videos is gaining value from the content and your presentation.
Good on ya.
Hi Dan,
Thank you for the nice feedback. I really appreciate your support and happy to hear you are benefiting from the training. Yes, it is definitely my passion to help others learn Excel & VBA. Thanks again and have a nice day! 🙂
Thanks Jon. Your first video regarding macros was very helpful and easy to understand.
Great video, looking forward to more. I am working on a spreadsheet to log employee work hours and pay rates.
Fantastic, easily understood videos Jon ! Enormously helpful ! Thank You !!!
Great intro to macros. I have developed many macros using the Marco recorder but always wanted to automate more. I was looking for a video, class, seminar on VBA. Thank you.
I want to prepare a macro which defines its own range from first active cell having data to last active cell that has data, post which it should be preparing pivot of that range.
Also if i filter data [same range] and i want to delete all rows that have values [that user will himself define in separate list], how should i do that because i dont want macro to get stuck if it doesnt find that value in data.
Hello Jon
I want to create a macro and would require your help regarding the same. This is regarding the automation for copy comments from old file to new file.
Its a report that needs to be reviewed every month. I want to copy the comments (updated in last column) from previous month report to New report so only new records can be addressed and old records can be filtered out. There are multiple reports and Concatenate, Vlookup takes a lot time.
I want to attach the report but there is no option to attach the document.
There are multiple rows and so has to concatenate the columns to make a unique cell in both old and new report and then to apply vlookup. I need your help to make this process automate. Could you please let me know how can we use macros or VBA coding in this scenario.
Thank you in advance.
I want to automate sending emails to individuals when the specific value in a cell is anywhere from 5 and 13. There is a specific Outlook template assigned for each number (5-13). I currently have code written, but am having the for each loop exit after sending only a handful of the emails. It’s never the same amount of emails sent. It works fine in my smaller test files, but when applied to a workbook with 200+ individuals, it only sends a few of the emails.
In the first video, i have understood about referencing, I was just copying VBA codes and most of the time it would not work. Today i have found you on Youtube after 3 weeks of searching. Now I am understanding the step by step coding in VBA especially on allowing VBA to assume worksheets and references.
I have been given a task to create a data manipulation program on a very large database program. the sources are form web programs, manual entries.
the program should Add, delete, remove duplicate, search and save in a different sheet/workbook according to criteria, use forms and have password.
If you could help
It would be wonderful
Regards
Gloria
I have really appreciated your teaching style. One of the problems that i am running into is that I have a very large spreadsheet made by someone else, the headers are on various rows and the data is simply copy and pasted to continue a daily bunch of data for each day of the month and every month of the year. I have struggled trying to figure out where to even start to extrapolate some of that data into useful pivot tables and charts. I realize that VBA will help but again not sure where to start because there are not necessarily specific total locations. The totals are just placed under the given chart that was created from the months data. I have signed up for the webinar but can you advise how to tackle large pieces of information that do not necessarily have a designated spot for totals.
Woow excellent this is my first experience with Macro thank you so much.i like the way you teaching.
Thank you Jon!
great Videos. Here is my challenge: I need to reference a closed workbook on our onedrive and update data daily in a second workbook.
Hi Jon,
I need your help to create a pivot table in vba. I have different ideas to create a pivot but its thrown type miss match error 13. Could you please solve this problem
Regards,
Gayatri
Thanks for breaking the creation of a macro down into the basics and relating it to something common like peeling a banana to eat it. This was helpful since I haven’t built macros from scratch. You are an excellent instructor!
Hi David,
You are a very good teacher. Being a beginner to VBA and macro, you have made it simple to understand!
Looking forward to the next lesson. Thank you.
Hey Jon
I watched your videos & attended your webinars & have become your fan since then. I also spreading a word & more about you in my circle & sharing your links. May God bless you & your family. Carry on the good work.
Easy to understand.
Hi Jon
Just joined. Seems like you can manage to really break the complexity of VBA coding into something fun and value adding in your daily work. Looking forward to following these these tutorials. /Kenneth
Jon,
Your first video was terrific! Have been a self trained Excel user for many years and now realize there is a much better way. Can hardly wait to learn more.
Thank you!
Reg
Very easy to understand even for beginners like me. Looking forward to the second video.
I want to automate the process of mining data from a public resource, combining that data with my own, and then exporting the summary data to a Word document. BTW…the first lesson was AWESOME!! You are CLEARLY a born educator!! SHC
Hi Jon,
I am trying to write a macro that will allow me to do the following example. I have a excel sheet with the following
Invoice number date amount product
Invoice number date amount product
Invoice number date amount product
Invoice number date amount product
Product number date amount product
I am asking is there a code you insert after the macro code to allow for additional lines to be captured from future reports that may have more lines then original report.
Hi Holly,
Great question! Yes, there are several ways to go about this. Here is an article and video series on 3 ways to find the last used cell on a sheet. I hope that helps get you started. Thanks! 🙂
Hi Jon
I am trying to create a macros that
copies information from one work boo to another macros enabled workbooks
the rows should be pasted on every 76th row starting from row 500 (i.e the first row on 500 then skip 75 then the next row of dater will be in 575. and the last row is 3000 after which the macro enabled workbk will not allow anything to be pasted.
how do i do this without affecting the code that is already in the workbook
what are the important concepts to take hid of?
I am not a macro beginner. But, this is still a good video to watch.
I have a time-concern question in my macro.
arr(500000,22) is my big array.
It reads 107286 records and catches 10 string columns and 12 number columns from 118 columns (the original file)
If there is the same item (based on my criteria), it will add the 12 number columns value together.
So, finally, it becomes 94516 records.
And, I want to write those “processed” data into a new file.
One column need to change the prod code to prod name. (The transfer-table is read)
The other one column need to change the zip code to 2 district-territory column. (The transfer-table is read too.)
So, it is 94516 x 23 output cells;
My question is…
It took 141 seconds (2:21 min) to process ans save the array.
But, it took 1438 seconds (23:58 min) to write.
(And, it took only 3 seconds to sort the output after I created)
How can I improve the “writing speed”?
My another sample is…
the raw data: 349247 x 118
processed to: 245586 x 23 (7:38 min)
write time: 87:08 min
sort time: 00:08 min
My biggest raw file is more than 500,000 records…
HOW CAN I improve the writing speed??????
Hi JJ,
When you write the output are you looping through each item in the array. If so, you can output the array to the sheet all at one time. You might to transpose the array when setting it to the range.
You can also turn calculation to manual when running the code if you are doing any other looping. Then turn back to automatic at the before the end of the macro.
I hope that helps.
Jon:
Thanks for your solution.
A.
My new code
ActiveSheet.Range(“A2”).Resize(recI, 23).Value = MyArray
did a wonderful job and did improve the speed a lot.
But, it also comes out some questions.
1. All “number-look” (id, zip code, …) cells have a green triangle in its up-left corner.
I can’t pad 0 to make id to have the same length and the sort function fail. (since 6>11 in string sort)
So, how can I remove those green triangle and pad 0? (format cell did not work.)
2. My array data start from index 2 (not 0). Can I paste array start from to instead of 0?
(If excel can’t do it, I can adjust my array to start from 0…
3. Try to apply the new way to the column title. But, it pastes the “C2” value to all cells. Why? How to change?
Dim colname() As Variant
Dim Rng As Range
Set Rng = ActiveSheet.Range(“C2:C13”) ‘ read the title’s new name
colname = Rng.Value
ActiveSheet.Range(“L1:W1”).Value = colname ‘ paste the new name to output sheet title
B.
I don’t understand your second way “…turn calculation to manual…” at all.
Can you explain more or give me the real code?
my raw data title: loc_idx, cust_id, pd_id, …, Jan, Feb, …
my goal: if the first 3 column are same, sum the value in each month together.
when I try to output the result, I need to transfer the pd_id to pd_name and transfer the loc_idx to state & city.
My code sort data first, read each row, compare and combine them, transfer pd_id and loc_idx, save to array then output.
How to apply your second way? Or, do you have other way to approach?
Thanks.
JJ
Great Job Jon!!! Thanks for sharing!
Hi Jon
I would like you to provide me vba code to copy worksheet from source workbook to another destination workbook and rename the worksheet partname of the source workbook with the current date copy Example Temp_14-04-2018
thank you kindly
I’m excited to learn more. Your videos are very easy to follow.
I’m just learning VBA and wish learn more. I’m creating a project time sheet to account for daily/weekly hours. I currently copy and paste from Excel to Access. Hoping to automatic this.
I want to know how to easily do macro in microsoft project. Thank you very much
This is in the nature of what I would call a “utility” macro rather than a macro that automates a common user activity.
I want to be able to determine the name (or “ID”), location (sheet, cell) of all the Data Tables in a workbook and then print out this information on a worksheet. I have worked with Date Tables a little in the past and I know that one of the inputs required when a Data Table is being created is the Row input cell and the Column input cell. I am guessing that that these are “properties”. If the “utility macro” I am looking for could provide information, cell address, about these 2 elements that would be great too.
Thank you in advance,
Bill Freund
Up until a couple of years ago I was programming in excel for years, but self taught. I decided recently to look at a refresher. I found this first video to be excellent. In fact even after all of my years of prior writing there were a couple of extra items I learned from this very basic video. Looking forward to the next.
I have a number of engineering applications written in excel that could use some updating?
I have a spreadsheet with 15,000 accounts listed in a table. Of the 15,000 account listings, 3,200 accounts have email’s entered. I can filter in the table for the accounts with emails entered or I believe I could use a macro to do that. Then I would like to keep one of each account and delete all the duplicates. (I would make a copy that i would keep for future reference. The result in this worksheet would be a file with all our accounts with emails with no duplicates. But we may still have more than one entry for some accounts if they have multiplications. We would have more work to do on this list but this would be a very good start. I have only rarely used macros or VBA’s and then by copy and pasting. The lesson was very clear and well structured and required uninterrupted concentration.
Hi Jon,
I’m totally new to VBA. Please pardon me if any of my questions sound silly.
I’ve not even began with the basics, not sure if the task I would like to try below would be too advanced. Will be happy if you could shed some light 🙂
I’m thinking of using VBA to combine several excel workbooks into one. In these workbooks, number of columns and headers are the same. However, there are some merged cells in some of the rows.
Hope you can offer some advice.
Please add my email address to your mailing list.
I want to learn about Macro & VBA, Excel etc.