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!
Excellent tutorial. Very easy to understand and follow. Would like to know how to find the last row with data in a worksheet and loop through it to extract data from a certain column.
Thanks!
Thanks David! I have an article and video series on how to find the last row with VBA. This should help get you started. In the next video in this series you will learn more about Loops. Thanks again and have a good one!
Hi Jon,
Thanks for this tutorial. Looking forward learning about VBA. One question though, could you make a tutorial on how to upload a certain excel file by clicking a command button and put to another worksheet (example worksheet 2).Thanks
Thanks Jerome! I’m not sure I understand your request. Do you want to copy a sheet to another workbook?
Hi Jon,
Thanks a lot for this instructional video. It really helps me a lot since I need to automate some of my reports to save time in creating the report from a file of numerous data.
Thanks JM!
Excellent video for complete novices like me
looking forward to expanding my excel knowledge with this
Another JA success 🙂
Thank you Bob! I really appreciate the positive feedback! 🙂
I am trying to automate the printing to Pdf of ranges (of different sizes) in multiple pages, but to create only one pdf file.
Very useful thank you. I have a work schedule showing when activities are supposed to start and finish, some of those activities are running concurrently. I would like excel to highlight in same color activities running at set periods of time.
Really awesome tutorial. God blessing U, Many thanks Jon.
Thanks Hery!
This is extremely helpful. The pace, style, and clarity in which you present is just perfect for me. I’m so excited to learn more. Thank you!
Awesome! Thank you so much for the kind words Gale! Happy to hear you are enjoying learning VBA. 🙂
Hi Jon,
I would like to know how to get excel to automatically send a e-mail to someone say if you have a expiry date reminder and you want to send a e-mail as a reminder?
Keep up the good work.
Regards.
Willem.
Thanks Willem! That is definitely possible with VBA. Ron de Bruin has some good resources on sending emails with VBA and Outlook. Thanks again!
Hi. I want to draw and animate shapes so I can develop interactive models and simulations.
Thanks for the video – As a “dummy” I will need to view it a few times!!!
I would like to be able to stop a macro running and select an area to copy and then select another area and paste that information elsewhere in the spreadsheet.
I would be grateful for any advice.
Thank you
Hi Roy,
That is something you can do with an InputBox. The InputBox prompts the user to make an input into a pop-up window, or select a range. I use this technique with the Paste Visible feature of my Paste Buddy Add-in. The InputBox can be created with one line of code, so you don’t have to create an entire userform like we do in video #3. I hope that helps. Thanks again!
Many thanks for your assistance.
Have a good day
Well Done, I’ve been using Excel for years without understanding Macros and VBA
Thanks John! Welcome to a whole new fun and exciting world. 🙂
Dear Sir
I like your videos very much. When will you upload part 2? I am preparing for an interview based on excel so Can you suggest some interview questions or can you suggest me which topics i have to cover.
Thanks
Jon,
This is excellent, first class information that is presented in a way that is very easy to understand and retain. Thank you very much for all that you give and do to make our learning experience the best possible. Everything is clear and concise and you always make yourself available to respond to any questions.
Respectfully,
Peter
Thank you Peter! I really appreciate your nice comment. 🙂
I am still having trouble with my code extracting info from certain cells in workbook 1 and placing information extracted into workbook 2 line after line. can you help please. I am very new to VBA. Rod
Great Job! have been self taught like many vbaers. do have some questions on code.
Need to copy values only (non-contiguous ranges) with a button of any of 20 sheets which are all identical (fill in the blanks if you will).
Then paste in any of the other 19 sheets (identical location), again non contiguous ranges.
Some ranges of the sheet are protected.
Here is the code i found and modified a little.
Sub apastebp()
‘
‘ apastebp Macro
‘unprotect sheet – having issue with this so removed
‘confirm use of button
myCheck = MsgBox(“WARNING! This bid page will be COVERED!. Continue?”, vbYesNo)
If myCheck = vbNo Then Exit Sub
‘continue if yes
‘
Dim sText As String
Dim DataObj As New DataObject
DataObj.GetFromClipboard ‘here I copy an empty cell”
‘ Set up for error checking
On Error Resume Next
‘ Get clipboard
sText = DataObj.GetText
‘ Clean non-printable characters
sText = Application.WorksheetFunction.Clean(sText)
‘ Could be one IF plus AND
‘ Check if error
If Err.Number 0 Then
MsgBox “Clip board empty”
On Error GoTo 0
Exit Sub
‘ Check if zero length (blank)
ElseIf Len(sText) = 0 Then
MsgBox “Clip board blank”
On Error GoTo 0
Exit Sub
End If
ActiveSheet.EnableSelection = xlNoRestrictions
Range(“C10:t48”).Select
Selection.FormatConditions.Delete
Range(“C10”).Select
Selection.pastespecial xlPasteAll
‘unprotect sheet – having issue with this so removed
‘final range location for cursor
Range(“h17”).Select
End Sub
thanks Jon.
dan
More Thanks
Thanks Jon!
that’s amazing way to teach VB MACROS. Simple and to the point. One word for your efforts …….. that’s “Awwwwwwwwwwwwwwwsome Stuff”
Thanks Muhammad! I really appreciate the great feedback! 🙂
Hi Jon,
Thanks for the video. I am practically a beginner and this video helped build up confidence in me because i have always been scared of programming. But you made it look easy. Thank you. This will definitely help in my job.
Have a nice day.
Awesome! I’m so happy to hear that Sree! Programming gets much easier with practice too. Thanks again and have a great weekend! 🙂
Thank John
Thanks for the video I am also beginner for the Macro I want to learn also how to automate my report. I hope you have many video to upload. Thank you very much
Thanks Francis! Yes, I have a lot of videos on learning VBA. I will be sending you more info on those in the coming weeks. Thanks again! 🙂
EXCELLENT. AWESOME. I thank for the lead taken to explain the VBA/Macros in a realistic, and simple way. Very very beneficial to all the persons who aspire to learn VBA/Excel. Thanks for the noble initiative taken to spread the VBA/Macros. What is the next video ? We are eagerly waiting to learn a lot from you.
Thanks Gopalakrishna! I am happy to hear you enjoyed the video. I will email you a link to the next video in just a few days. Stay tuned. 🙂
Jon,
Great job on the video… it was extremely informative and very easy to comprehend…Thank you
Thank you Sam! I really appreciate that. 🙂
Hi,
I am trying to compare 2 work books data with macros.Could you help me?
Fantastic Jon, I am a professional Excel user, but I still learn a lot from your videos. You are amazing at teaching. Thank you so much
Thank you Ben! I really appreciate the nice feedback. Have a great day! 🙂
Hi Jon,
thanks very much for that excellent video and very easy to follow! i’m a new learner of VBA and lots of tasks come into me on that.
hope you can update on the next 2 videos so that i can save lots of time surfuring from internet and google… 🙂
Thanks Charles! I will email you the next video in just a few days. I hope you learn a lot from it. Have a good one! 🙂
Hi Jon,
Its great vidoe online for the macros learners, i am a very new learner. Hope you can update on the other 2 videos which would be more helpful..
Jon…Really it was helpful to know about properties & methods in easy way 🙂 thanks a lot.
until now I was learning the macro code without knowing the basics it seems..
I would like to know more about VBA macro. Please help me with this too,
I need to concatenate N number of cells from column A like (A1:AN,N->its varies for every inputs) and those output should be saved in another cell say B1.
Do we need lopping concept for this???
Thanks Priya! I’m happy to hear you are learning from the videos. I didn’t know about properties and methods for a long time either when I started coding.
Here is an article I have on how to concatenate multiple cells with a macro. It includes the code, and yes, a loop is involved. Check it out and let me know if you have questions. Thanks!
Excellent video. Good analogies, easy to understand.
I would like to create a macro that lookup the value in sheet 1 cell A1 through all the workbooks in a
determined folder and generates a list with all these values.
Thanks Javier! Great question! In the next few videos you will learn how to loop through the worksheets in a workbook. This concept of looping can also be applied to all the workbooks in a list or specific folder.
I have an entire module dedicated to that topic in my VBA Pro Course. There you learn how to create an application I call the File Manager. It allows you to run any type of macro or code on a set of files. It’s great for automating the same task on multiple files like reports or budget templates.
I will send the next video in the series in just a few days. Thanks!
I am looking for something similar. I have a spreadsheet with exported transactions from my program. I want to transfer “some” of the data to a new spreadsheet. So basically, I do not want all of the columns from the first worksheet as they are not needed, but I want ALL of the rows for each column that I want to copy over. I will not know ahead of time how many rows will be in the transactions spreadsheet. It could range from 1 to 5000.
Hi Kathleen,
Great question! Here are a few additional resources that may help you.
How to find the last used cell in the sheet
3 Ways to copy and paste with VBA
Those articles and videos help explain how to find the last used row, column, or cell. And also how to copy and paste with VBA.
I hope that helps. Thanks again and have a great day! 🙂
Hi Jon
Great video. Excellent intro to VBA Macros.
My area of interest is data analysis.
Kind Regards
Ilan.
Thanks Ilan!
Hey Jon,
Very nice video. Like the simplistic approach. Keep it up.
Thanks!
Thanks Nebiyou!
Hi Jon,
I have just been handed off an excel spreadsheet to use for daily reporting purposes that includes macros that are not working. I need to learn how to use macros and apparently debug them. The problem is, I know nothing about macros, never used them and had no idea what they could do till now. I am going to start with your free course above, macros and vba training. Do you recommend any books that might be a good desk top reference?
Any advice and help would be greatly appreciated….feeling a little overwhelmed, looks Greek to me lol!
Hi Melissa,
Sounds like you got tasked with quite the assignment! 🙂 Well, hopefully I can help make macros less scary. I know that it is overwhelming when you first get started. It’s just like learning Excel. There are a lot of tools and VBA has a massive library, but you will get more comfortable with it over time.
In regards to books, I really like “Excel VBA Programming for Dummies” by John Walkenbach. I have it listed on my resources page, along with a few other books I like. That book is shorter than some of the other VBA books, and I think it is a good place to start. It doesn’t feel as overwhelming.
I also have an entire course on macros & VBA called The VBA Pro Course. I will be sending you more info on that in the coming days. I hope that helps. Let me know if you have any other questions. Thanks!
Thank you so much for getting back to me so quickly! Yes I do remember a time when I thought I would never catch on to Excel but I have 🙂
I will definitely order one of those books. I watched the how to write your first macro and I am looking forward to the next video in this series.
So happy I found you online!
Learning Excel and VBA definitely takes more time than we anticipate. It took me a long time to learn too, but the rewards are well worth it. I hope to make it a little faster and easier for others with these videos and courses. Happy to have you here! Thanks again!
Great video, had some debug errors the 1st attempt with my macro and had to start over. 2nd attempt I went slower and paused video as I went step by step and worked fine the 2nd time.
Thanks George! That is great that you took the time to try again. Learning to code can be challenging and overwhelming at first, but it does get a lot easier with practice and experience. Please let me know if you have any questions. Thanks again!
Jon,
I have been looking for a video like this for quite a while.
You are to the point, clear and concise.
The lesson is very complete, with shortcut keys etc.
In will be watching all of the lessons available.
Thank you for making this available.
Scott
Thank you Scott! I really appreciate your kind words and happy to hear you are learning VBA. 🙂
Hi Jon,
Thank you so much, this is so helpful and so easy to understand. Thank you
for creating this platform.
what process do I want to automate in Excel: –
1) I need to highlight blank cells in my list of signed reports received.
2) I then need to =counta() those that have been received and =countblank()
for those still outstanding
3) I need to do this for 9 departments(9 worksheets) and a summary sheet
for all departments in one sheet.
Hi Maggie,
Thanks again for the great feedback! I think you will find video #2 helpful for creating some of this macro. You can use some of the Excel functions like COUNT in VBA.
The following line of code can be used in VBA. You will specify the range to apply it to in the parenthesis and also set the outcome to a variable.
So the final line of code might look like the following.
lCount = Application.Worksheetfunction.CountA("A1:A1000")The CountBlank worksheet function is also available in VBA.
I hope that helps. Let me know if you have any questions. Thanks! 🙂
Thanks ! I would like to create macros for pivot tables and put all in one spreadsheet.
Thanks for an astounding video, I hope your keep the good work in the future.
Thanks Jon,You are great , introduced great things in Excel to me ,hats off to you
Hi Jon,
I need to automatically populate debit and credit from the “Source” tab to multiple tabs (sub tab) within the same workbook. The “Source” tab contains multiple blocks of data(range), each range of data contains 4 to 6 rows or 2 to 4 rows, various from range to range, each range is separated by 2 blank rows. The data contains Account string, debit and credit, the account mostly stay unchanged, just debit and credit need to be updated. the sub tabs are already there, I need to automatically update the debit and credit. Each range is associated with the value in each sub tabs. there are 10 sub tabs, this sub tabs can increase if the new range is been added.
I have VBA code written, it only works for the range doesn’t increase.
I can send you the code if you want.
Greatly appreciate your knowledge!
Sincerely,
Jean
It’s too much helpful for me in my office work now easily I complete my work on time.
Thanks
Hi, this is my first time of watching your excel campus video, I want to say, thank you for the video, however, it is too early for me to make any comment so far, nevertheless, I look forward to your forthcoming video and see how they go, that is the time I will be able to make a genuine comment.
Awesome! I’m happy to hear you enjoyed them Sulaiman. 🙂
Hi , Jon dear
Thanks for learn VBA and other learn excel,can i visit video2 and video3 VBA ? what is ways?
Hi Jon thank you for sharing this fabulous videos.
my goal is to auto some reports that I make daily but they are not of the same amount of rows and I need to formated and make some formulas to work on the bottom off the report,but is never the same length
Thanks Hector! That is a great question. I have an article and video series on how to find the last used cell with VBA, for situations like this where number of rows in the data set changes. There are a few ways to go about it, and the article covers all of the most common ones.
Hi Joe,
Your Macros videos are very useful to as i am new learner in macro am i find it very easy to understand.
Thanks for Your Videos.
I have one doubt.
In my office i am working on one project in which i have to copy data from one excel workbook which is at one location and paste to my master workbook which is other location with specific criteria while copying the data (i.e it doesn’t contain “Total” in column name called Name i need your help how to automate these by using VBA.
Thanks in Advance
Thanks Nilesh! That can definitely be done with VBA. You might want to checkout my article and video series on how to find the last used cell in a sheet. The Range.Find method I explain there can be used to find other criteria like Total Rows. You could also use AutoFilters in VBA to filter out rows. I hope that helps. Thanks!
Hi Jon
Many thanks for all the videos you put out. I’m finding them really useful. I haven’t watched this series yet so will comment on them later on.
My goal at the moment is to be able to create a salary monitor between two spreadsheets – the budget planner and the payroll report (both by different providers). If you have any tips or solutions that would help, that would be really appreciated. Many thanks.
Thanks Diane! I’m happy to hear you found the videos useful. I don’t have any articles on that exact scenario, but you could definitely use VBA to copy the data into one file and then create a comparison report with formulas or pivot tables. Here is an article I have on how to compare data sets with a pivot table. This is much easier than trying to write lookup formulas. I hope that helps.
Hi Jon
I am using a text file daily importing by using From Text in excel. the file consists of 200 units but each ID no. is split into two rows therby doubling the number of records and also the pages for printing. I would like to merge two rows into one and also delete unwanted rows.I am doing by concatenating but it has to be copied to each alternate row. I would like to get a macro for this as I have to do this daily. Please help me.
Hi Jon
I use a text file daily by importing in Data – From Text. But each ID No. is split in two rows and doubling the number of rows and wasting double the paper for print. I want to use a macro for merging two rows into one single row, and also deleting unwatned rows. There are 200 ID nos. every day. I have tried to use concatenate, but I have to copy the formula for 200 rows. Please help.
Sample file.
Information For Date: :24/05/2016 Page No : 1
______________ ______________ ________________ ______ ____________________ ____________________ _______________________________________
ID no. PLACE RECEIPTS EXPENDITURE DIFFERENCE(C6-D6) REMARKS
______________ ______________ ________________ ______ ____________________ ____________________ _______________________________________
SBIN0000268 PODILI
32 844252 11 1577194 -732942 Partial
SBIN0000750 SRISAILAM PR
6 330610 0 0 330610 Partial
SBIN0000753 NARASANNAPET
26 596143 4 153672 442471 Partial
SBIN0000754 TO – MAHARANIP ETA
74 3806531 39 6498138 -2691607 Partial
SBIN0000761 RAYACHOTI
47 947905 11 774610 173295 Partial
SBIN0000766 PALAKONDA
0 0 2 762506 -762506 Partial
SBIN0000767 CHIPURUPALLE
13 227819 7 362329 -134510 Partial
SBIN0000773 SULURPET
49 880311 1 41490 838821 Partial
DESIRED FORMAT
ID NO. PLACE NO. RECEIPTS NO. EXPENDITURE DIFFERENCE REMARKS
SBIN0000268 PODILI 32 844252 11 1577194 -732942 Partial
SBIN0000750 SRISAILAM 6 330610 0 0 330610 Partial
SBIN0000753 NARASANNAPET 26 596143 4 153672 442471 Partial
ETA
SBIN0000754 TO-MAHARANIPETA 74 3806531 39 6498138 -2691607 Partial
SBIN0000761 RYACHOTI 47 947905 11 774610 173295 Partial
SBIN0000766 PALAKONDA 0 0 2 762506 -762506 Partial
SBIN0000767 CHIPURUPALLE 13 227819 7 362329 -134510 Partial
SBIN0000773 SULURPET 49 880311 1 41490 838821 Partial
Hi Jon
I use a text file daily by importing in Data – From Text. But each ID No. is split in two rows and doubling the number of rows and wasting double the paper for print. I want to use a macro for merging two rows into one single row, and also deleting unwatned rows. There are 200 ID nos. every day. I have tried to use concatenate, but I have to copy the formula for 200 rows. Please help.
Sample file.
Information For Date: :24/05/2016 Page No : 1
______________ ______________ ________________ ______ ____________________ ____________________ _______________________________________
ID no. PLACE RECEIPTS EXPENDITURE DIFFERENCE(C6-D6) REMARKS
______________ ______________ ________________ ______ ____________________ ____________________ _______________________________________
SBIN0000268 PODILI
32 844252 11 1577194 -732942 Partial
SBIN0000750 SRISAILAM PR
6 330610 0 0 330610 Partial
SBIN0000753 NARASANNAPET
26 596143 4 153672 442471 Partial
SBIN0000754 TO – MAHARANIP ETA
74 3806531 39 6498138 -2691607 Partial
DESIRED FORMAT
ID NO. PLACE NO. RECEIPTS NO. EXPENDITURE DIFFERENCE REMARKS
SBIN0000268 PODILI 32 844252 11 1577194 -732942 Partial
SBIN0000750 SRISAILAM 6 330610 0 0 330610 Partial
SBIN0000753 NARASANNAPET 26 596143 4 153672 442471 Partial
ETA
SBIN0000754 TO-MAHARANIPETA 74 3806531 39 6498138 -2691607 Partial
Hi Jon
Please would you show a macro which will copy 14 sheets containing data, all begining with WK, eg WK1, WK2, etc then append the data from each sheet by pasting all to a sheet in the same work book call Consolidated Data?Hope that made sense.
Thanks
Sara
Hi Sara,
Great question! I actually have a tutorial that explains how to consolidate sheets in my VBA Pro Course. The process is a little more in-depth, but basically requires you to loop through the sheets and copy/paste the data to a consolidated data sheet. Thanks again!
Fab, thanks Jon 🙂
hi john., i created an array formula to compare values of sheet1 to sheet2 and extract only the unique.. how can automate it?may sheet1 contain 2000 rows and my sheet2 7000 rows…
Hi Jon
I have created a roster on an excel spreadread with 3 columns that have the names of staff members, their email addresses and the dates of the month on which they are scheduled to fulfil a particular task. How do I write a code so that an email can be automatically send to the person mentioned on the same row for which his date is today().
Thanks
Hi Gregs,
Great question! Ron de Bruin’s website is probably the leading resource on this topic. Checkout this page that contains code examples for sending emails from Excel lists. I hope that helps. Thanks again!